This is my query:
SELECT COUNT(*)
, DATE(created_ts)
FROM users
GROUP BY DATE(created_ts);
if the user selects a time range, such as
2024-12-17 00:00:00–2024-12-17 15:59:59
but a problem arises here. The user may be located in Shanghai, and the time sent to me by the front end is always UTC time.
The time the server received it was:
2024-12-16 16:00:00 – 2024-12-17 15:59:59
SELECT COUNT(*)
, DATE(created_ts)
FROM users
WHERE NOT created_ts < '2024-12-16 16:00:00'
AND created_ts <= '2024-12-17 15:59:59'
GROUP BY DATE(created_ts);
then the execution of this sql will generate groups No. 16-17.
this will cause COUNT(*) to be inaccurate. Please tell me how to deal with it.
I don’t know what to do, I hope the query results are what the user wants, that’s all
loyoqq is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
- Make the column a
timestampTZand make the client send you atimestampTZas well, to remove the ambiguity entirely.alter table users alter column created_ts type timestampTZ;They are time zone aware timestamps that cost exactly the same, while a regular
timestampis just a “picture of a clock” that’s causing problems like the one you’re facing. If your client is already sending you a UTCtimestampTZ, you only need to make sure the column is altered and you should be fine. - By default,
timestamps use microsecond resolution. Shifting the upper bound one second down while making it inclusive will cause problems – just use 16:00 for both and make one inclusive with>, one exclusive with<=(your first bound comparison was flipped by the way):
demo at db<>fiddleSELECT COUNT(*) , DATE(created_ts) FROM users WHERE NOT created_ts > '2024-12-16 16:00:00' AND created_ts <= '2024-12-17 16:00:00' GROUP BY DATE(created_ts);Otherwise, there’s a full second running from
15:59:59.000001up to15:59:59.999999that won’t belong to any range. - If your client is sending you a time zone unaware
timestampbut you both agreed it’s in UTC, you can useat time zoneto apply the shift and make it time zone aware:SELECT COUNT(*) , DATE(created_ts) FROM users WHERE NOT created_ts > '2024-12-16 16:00:00'::timestamp at time zone 'UTC' AND created_ts <= '2024-12-17 16:00:00'::timestamp at time zone 'UTC' GROUP BY DATE(created_ts);Making sure the session that runs this query opens with
set TimeZone='UTC';would have the same effect – comparingtimestamptzto atimestampmakes Postgres prefer casting the latter totimestamptzrather than losing the time zone. And when it casts atimestampto atimestamptz, it assumes it’s in the time zone indicated by the currentTimeZonesetting.