PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Selecting todays records in postgres

I have a table with a field called recordTime of type timestamp without time zone. I want to select todays records only (i.e. since midnight). The records are sourced globally and arrive as UTC, so I need to select records 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)

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search