Search code examples
postgresqltimestamptimezone

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.)

Solution

  • Correct, short, and fast

    WHERE recordtime >= date_trunc('day', now() AT TIME ZONE 'UTC') 
AND   recordtime <  date_trunc('day', now() 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.)

    The definition of "today" depends on the current time zone. Use the AT TIME ZONE construct.
    date_trunc('day', now() AT TIME ZONE 'UTC') gets the start of the current day at time zone UTC. 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, especially to use a B-tree 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.

    Smarter

    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):

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

    Aside

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