How to get the latest data for 24 hours in postgreSQL

Question

I want to get the last 24 hours of data. I wrote a query in postgreSQL as follows. But I could not get the answer as I expected.

SELECT startdate::timestamp AS startdate, (DATE_PART('hour',startdate::timestamp)::integer) as hrs,count(guorderid) FROM ord_entitlement WHERE DATE_PART('Day',CURRENT_DATE::timestamp - startdate::timestamp) < 1 AND DATE_PART('hour',startdate::timestamp) <= 24 GROUP BY hrs,startdate ORDER BY startdate 

Answer 1

If you use CURRENT_DATE , you will not get the time instead of using the now() function. Try the following:

  SELECT startdate::timestamp AS startdate, (DATE_PART('hour',startdate::timestamp)::integer) as hrs,count(guorderid) FROM ord_entitlement WHERE DATE_PART('Day',now() - startdate::timestamptz) < 1 GROUP BY hrs,startdate ORDER BY startdate 

Answer 2

Instead of checking the details of the date, do the math of time to get the interval. Use NOW () to get timestamptz.

 SELECT startdate::timestamp AS startdate, (DATE_PART('hour',startdate::timestamp)::integer) as hrs, count(guorderid) FROM ord_entitlement WHERE NOW() > startdate::timestamptz AND NOW() - startdate::timestamptz <= interval '24 hours' GROUP BY hrs,startdate ORDER BY startdate 

This ensures that you get the last 24 hours no matter what your time zone or daylight saving time says. NOW() > startdate::timestamptz ensures that you don't accidentally pick things up from the future.

Answer 3

date_part() works like extract() , i.e. it extracts a subfield from the source:

 -- they will both yield 9 as result select date_part('day', date '2015-01-09') "day part of 2015-01-09", date_part('day', date '2015-02-09') "day part of 2015-02-09"; 

The extraction day(s) therefore not suitable for selecting the last 24 hours. Similarly, extracting hour(s) will (almost) always give less than or equal to 24 .

Extracting day(s) from interval (which is the result of subtracting 2 timestamp s) is slightly different. The result may depend on whether the interval is justified or not:

 -- they will both yield 1 as result select date_part('day', interval '1 day') "day part of 1 day", date_part('day', interval '1 month 1 day') "day part of 1 month 1 day"; -- they will yield 1, 32 and 397 respectively select date_part('day', timestamp '2015-02-09' - timestamp '2015-02-08') "interval 1", date_part('day', timestamp '2015-02-09' - timestamp '2015-01-08') "interval 2", date_part('day', timestamp '2015-02-09' - timestamp '2014-01-08') "interval 3"; 

Depending on the fact that subtracting the timestamp does not give reasonable intervals, I do not think this is the best option. You can use simpler conditions to achieve your goal:

 -- if startdate is a timestamp: where current_timestamp - interval '1 day' <= startdate -- if startdate is a date: where current_date - 1 <= startdate 

If you want to also ban future dates (as your question suggests), you can use one condition between :

 -- if startdate is a timestamp: where startdate between current_timestamp - interval '1 day' and current_timestamp -- if startdate is a date: where startdate between current_date - 1 and current_date