CODEWITHSUNDEEP
postgresqltimestamptimezone
ConanTheGerbil
ConanTheGerbil

Reputation: 787

Selecting today's rows based on UTC time

I have a table with a field called recordTime of type timestamp without time zone. I want to select today's rows only (i.e. since midnight). The records are sourced globally and arrive as UTC, so I need to select rows based on midnight UTC.

There seems to be a myriad of ways of doing this including ...

WHERE date_trunc('day', recordTime) = current_date ;

WHERE date_trunc('day', recordTime) = date_trunc('day', current_date at time zone 'utc') 

WHERE date_trunc('day', recordTime) = date_trunc('day', current_timestamp at time zone 'utc') 

WHERE recordTime >= '17-May-2024 00:00:00'

Which of these is best practice - or maybe a different method entirely? (Bearing in mind that performance seems similar in all cases.)

Upvotes: 2

Views: 258

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658392

Correct, short, and fast

Requires Postgres 12 or later:

WHERE recordtime >= date_trunc('day', now(), 'UTC') AT TIME ZONE 'UTC'
AND   recordtime <  date_trunc('day', now(), 'UTC') AT TIME ZONE 'UTC' + interval '1 day';

The 2nd line is optional if there cannot be rows past "today" in the table. Would waste only very little performance in that case. But another filter can lead to different (inferior) query plan as Postgres (incorrectly) estimates fewer result rows.

The definition of "today" depends on the current time zone. Provide that explicitly to get immutable results. date_trunc('day', now(), 'UTC') gets the start of the current day in the time zone UTC. The result it type timestamptz. Convert to the local timestamp with the AT TIME ZONE construct See:

now() is Postgres short syntax for standard SQL current_timestamp. See:

Include the starting 00:00, but exclude the ending 00:00.
Don't use BETWEEN with timestamp types.

Make the expression sargable. I.e., compare the bare column to a (computed) constant, not the other way round. Better in any case, but essential to use an index on recordtime - which you should have unless the table is trivially small.

Shorter

If you are sure the timezone setting of the current session is 'UTC'.
One way to make sure is to set it yourself in the same transaction:

BEGIN;
SET timezone = 'UTC';

...
WHERE recordtime >= current_date
AND   recordtime <  current_date + 1;

You can add integer to a date to add days. And date is coerced to the column type timestamp [without time zone] cleanly in the expression.

But if time zones can be involved in any way, it's typically better to work with timestamptz columns to begin with. Then, to get rows for "today in the time zone UTC", use the convenient variant of date_trunc() taking the time zone as 3rd parameter (since Postgres 12) like demontrated at the top.

Aside

Avoid mixed-case identifier in Postgres if at all possible. Else, you introduce uncertainty whether it's really recordtime or "recordTime". See:

Upvotes: 3

Schwern
Schwern

Reputation: 165396

WHERE date_trunc('day', recordTime) = date_trunc('day', current_timestamp at time zone 'utc')

I don't know if this is best practice, but it has the advantage of being explicit about its conversions. It also avoids the gotcha of accidentally matching midnight the next day.

You can shorten it to:

WHERE recordTime::date = (current_timestamp at time zone 'utc')::date;

WHERE recordTime >= '17-May-2024 00:00:00'

Incorrect. This will match every recordTime after May 17th, including May 18th, May 19th, etc.

You'd instead write...

WHERE '17-May-2024'::date <= recordTime and recordTime < '18-May-2024'::date

This will match 17-May-2024 00:00 and will not match 18-May-2024 00:00.

You have to input the date manually, but that's a simple matter if you're running the query from a program.

Watch out using between. For example, where recordTime between '17-May-2024'::date and '18-May-2024'::date. This will match both dates. where recordTime between '17-May-2024'::date and '17-May-2024'::date is correct, but odd. It's better to write it out long-hand.

current_date is the date according to the current time zone, but it has no time zone information, so that's no good here. Also don't use synonyms like today and tomorrow.

test=# set timezone='Asia/Tokyo';
SET

test=# select current_date, 'today'::timestamp, 'tomorrow'::timestamp;
 current_date |      timestamp      |      timestamp      
--------------+---------------------+---------------------
 2024-05-18   | 2024-05-18 00:00:00 | 2024-05-19 00:00:00
(1 row)

test=# set timezone='America/Los_Angeles';
SET

test=# select current_date, 'today'::timestamp, 'tomorrow'::timestamp;
 current_date |      timestamp      |      timestamp      
--------------+---------------------+---------------------
 2024-05-17   | 2024-05-17 00:00:00 | 2024-05-18 00:00:00
(1 row)

Finally, the best thing for performance is to ensure recordTime is indexed.

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 127416

Today starts at midnight 00:00 and ends "tomorrow" also at midnight. Just use today and tomorrow:

WHERE recordTime BETWEEN 'today'::timestamp AND 'tomorrow'::timestamp

It's fairly straight forward and easy to read and understand. You can even do some additional interval calculations on these values, if needed.

Upvotes: 0

Related Questions