How to get last 24 hrs data in postgreSQL

Question

I want to get last 24 hrs data. i wrote a query in postgreSQL as follows. But I couldn't 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

Instead of checking date parts, do the time math to get an interval. Use NOW() to get a 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 you will get the last 24 hours no matter what your time zone or daylight savings says. NOW() > startdate::timestamptz ensures you don't accidentally pick up things from the future.

Answer 2

date_part() works like extract(), i.e. they will extract 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";

Extracting day(s) therefore is not suited to select the last 24 hours. Similarly extracting hour(s) will (almost) always yield less than or equal to 24.

Extraction day(s) from interval (that's the result of substracting 2 timestamps) is a little different. The result might 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 the timestamp subtraction is not giving justified intervals is not the best option, I think. You could 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 disallow future dates too (as your question's title suggests), you could use a single between condition:

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

Answer 3

If you use CURRENT_DATE you will not get the time instead use 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