1

I have a PostgreSQL table named testing with a column named creation_time as timestamp with time zone. The database timezone is UTC

Now I want to get all rows whose time is greater than 00:00 of the current day as per the timezone "America/New_York".

I know how to get all rows after local midnight:

SELECT * FROM testing
WHERE  ( creation_time >= now()::date)
ORDER BY id DESC

But how to use this query with a different timezone?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Santhosh
  • 9,965
  • 20
  • 103
  • 243

1 Answers1

1

Assuming "the current day" is also defined by NY time, not by the current timezone setting.

SELECT *
FROM   testing
WHERE  creation_time >= date_trunc('day', now() AT TIME ZONE 'America/New_York') AT TIME ZONE 'America/New_York'
ORDER  BY id DESC;

Yes, AT TIME ZONE 'America/New_York' twice. No typo there.

now() AT TIME ZONE 'America/New_York') gets local NY time. date_trunc gets 00:00 of that day. The 2nd AT TIME ZONE 'America/New_York' converts the local time back to timestamptz, which we finally compare to.

If you want NY 00:00 of your local date, it's simpler:

WHERE  creation_time >= CURRENT_DATE::timestamp AT TIME ZONE 'America/New_York'

Same time, but can be a different day!

CURRENT_DATE is the local date (date according to the time zone setting of the current session). Effectively the same as now()::date.

Further reading:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228