16

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?

Improve this question

3 Answers 3

Reset to default
22

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

enter image description here

update:

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

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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.
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.
'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.
Any way to specify "Pacific" and have the DB figure out if it should be standard or daylight savings?
22 
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...

Improve this answer

2 Comments

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.
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.
1

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).

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.