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

    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: