How to get all data post midnight of different timezone?

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?

Answer

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:

• Ignoring time zones altogether in Rails and PostgreSQL