CODEWITHSUNDEEP
postgresqldatetime
sebastiengiroux
sebastiengiroux

Reputation: 107

Specifying the timezone of a date in the WHERE clause of a Postgres SQL query

In Postgres, I always store my dates using timestamp with time zone.

For the sake of the discussion, let assume I have to store events at the time (8am, 1pm and 10pm) in Eastern Time Zone (EST -04) on the 2018-01-16. The timestamp in the database would be :

  • 8 am: "2018-01-16 12:00:00.000+00"
  • 1 pm: "2018-01-16 17:00:00.000+00"
  • 10pm: "2018-01-17 02:00:00.000+00"

How would I write a Postgres SQL query to get all events that occurred on the 2018-01-16 EST?

Upvotes: 6

Views: 7422

Answers (2)

coladict
coladict

Reputation: 5095

SELECT fields, stamp FROM table WHERE stamp < '2018-01-16T00:00:00.000-04:00'

PostgreSQL accepts ISO 8601 format and standard SQL format.

You can also use =, <=, =, >=, > and BETWEEN, which is inclusive.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246093

The AT TIME ZONE construct is your friend:

WHERE CAST((tscol AT TIME ZONE 'EST') AS date) = DATE '2018-01-16'

Upvotes: 11

Related Questions