How to return current date in a different timezone in PostgreSQL

Question

I'm working on an application which uses Eastern time with a database set to Pacific time. This has been causing some issues, but we're told that it can't be any other way, so we just have to work around it.

Anyway, one of the things I'm having trouble with is getting today's date. Since the database is in Pacific, if I ask for today's date using current_date at, say, 1AM, it'll give me yesterday's date. I've tried setting timezone and adding/subtracting intervals, but it never seems to work the way I expect it to, and I'd rather not have to do extensive testing at 1AM to get this to work.

Is there a somewhat simple way to return a DATE for today's date in a given time zone in PostgreSQL?

Unavailing

Answer 1

select current_date at time zone 'UTC',current_date::timestamp ; or any other zone

update:

select (current_date at time zone 'UTC')::date,current_date::date

Debtor

This answer is giving me wrong results with Postgres 9.6.5. As I write this, it is 2017-09-29T05:00Z (I am in UTC+10 and that is my Postgres timezone), yet select (current_date at time zone 'UTC')::date is saying 2017-09-28. By contrast, select (current_timestamp at time zone 'UTC')::date is giving the correct UTC date right now of 2017-09-29. – Hellespont

I believe this is actually converting the current date (in the db tz) to the given tz. Not what the current date is in that tz. – Workwoman

'at time zone ..' first converts the date from current_date into a date + timestamp; setting the time to 00:00. Then, it adds whatever the timezone offset is. If it's e.g. 2/2, 4pm in London, and you do this for 'Australia/Sydney', you will still get 2/2 (because it would be 2/2 00:00 + 11 = 2/2 11AM). Instead, you want to use current_timestamp, as @SimonKissane suggests. – Parliament

Any way to specify "Pacific" and have the DB figure out if it should be standard or daylight savings? – Tejeda

Answer 2

SELECT date(timezone('EST', now()))

will return the current date in Eastern Standard Time regardless of database time zone

(or any other time zone, will however NOT work with offsets, for whatever reason...)

SELECT date(timezone('UTC±XX', now()::timestamp))

if you want to use offsets, this will only work with UTC offsets though...

Usher

The reason the above does not work with UTC offsets is because postgresql assumes the given value is a POSIX time-stamp, which is essentially identical to a UTC offset, but with an inverted sign and completely arbitrary letter combination. – Usher

Coming back to this post I realize the above comment might be a tad bit unclear, what I meant was that the first code snippet would also work with UTC offsets, but you would have to invert the sign to convert it to a POSIX time-stamp. Eg to get the current time in UTC+X you would have to write UTC-X instead. – Usher

Answer 3

Either of these should handle daylight savings time for Eastern Time:

select date(timezone('EST5EDT', now())), date(timezone('America/New_York', now()));

The list of timezones recognized by Postgresql can be queried from the views pg_timezone_abbrevs and pg_timezone_names.

To illustrate, with a Postgresql server that uses UTC (I'm in MDT):

select now(), timezone('America/New_York', now() - interval '15 hours'), timezone('America/New_York', now() - interval '15 hours' - interval '2 months');
              now              |          timezone          |          timezone
-------------------------------+----------------------------+----------------------------
 2024-03-21 18:20:55.543399+00 | 2024-03-20 23:20:55.543399 | 2024-01-20 22:20:55.543399
(1 row)

I subtracted 15 hours to show that it handles the date correctly when crossing midnight. The third column also goes back 2 months to show standard time (EST), whereas the second column is in daylight savings time (EDT).

Mccrae