Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

postgresql - Getting date with timezone offset

I am trying to extract the date from a query in postgres. The timestamp is stored as UTC, so if I have 1/1/2014 02:00:00, I want the date in pacific time, to be 12/31/2013, not 1/1/2014. I am really close, but both query 2 and 3 still return 1/1/2014.

SELECT '1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles';

returns

2014-01-01 02:00:00-08

-

SELECT CAST('1-1-2014 02:00:00'::timestamp at time zone 'America/Los_Angeles' AS Date);

returns

2014-01-01

but I want it to return 2013-12-31.

SELECT CAST('1-1-2014 00:02:00'::timestamp at time zone 'America/Los_Angeles' AS Date) at time zone 'America/Los_Angeles';

returns

2014-01-01 00:00:00

but I want it to return 2013-12-31 00:00:00

I basically want to return the date in the timezone it is in, in this case the pacific timezone.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

If it is timestamp without time zone you need first to tell it to use the UTC time zone and then convert to another time zone:

SELECT '1-1-2014 02:00:00'::timestamp at time zone 'UTC' at time zone 'America/Los_Angeles';
      timezone       
---------------------
 2013-12-31 18:00:00

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...