CODEWITHSUNDEEP
postgresql
Krokodyle
Krokodyle

Reputation: 815

records from yesterday in postrgesql

I have an application in which I've used MySQL. I had a report that stretched records of the last 24 hours. I used the query:

WHERE (DATE_SUB(CURDATE(), INTERVAL 1 DAY) <= FROM_UNIXTIME(`workorder`.`CREATEDTIME` / 1000))

Now I have to use PostgreSQL and do not know how to make a report of the last 24 hours. Can any of you help?

Upvotes: 80

Views: 93474

Answers (4)

Basil Bourque
Basil Bourque

Reputation: 338326

> TIMESTAMP 'yesterday'

For convenience, Postgres includes a few hard-coded values as special Date/Time inputs. They include:

  • yesterday
  • today
  • tomorrow
  • now

Try SELECT TIMESTAMP 'now'.

For example, here is a query.

SELECT when_row_created_
FROM customer_
WHERE when_row_created_ > TIMESTAMP 'yesterday' 
ORDER BY when_row_created_ DESC
;

These commands may not be appropriate to production code, but they certainly are handy in development. Read the docs and do some practice to be sure you understand the behavior of these commands, how the session’s time zone affects them and so on.

Downsides include (a) implicitly ignoring the crucial issue of time zone, and (b) not standard SQL.

Upvotes: 56

Lucky Ratnawat
Lucky Ratnawat

Reputation: 379

WHERE workorder.createdtime::date = current_date - 1; --from yesterday

Upvotes: 6

roman
roman

Reputation: 117345

where workorder.createdtime >= now() - interval '24 hour'

Upvotes: 10

Curt
Curt

Reputation: 5722

WHERE workorder.createdtime > current_date - 1     -- Yesterday and today

WHERE workorder.createdtime > current_timestamp - interval '1 day' -- last 24hr

Upvotes: 131

Related Questions