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?

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

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