postgresql: Change timestamp timezone part without shifting hours?

Question

I have a timestamp field in my table that has stored the data in UTC-10. However, the correct timezone is UTC+10

I would like to change the timezone part to UTC+10 without shifting the hours. Only want the system to consider the same timestamp as UTC+10 instead of UTC-10

If I do it like

select email_timestamp as time zone 'UTC+10' from emails

then it also shift the hours.

Can someone please tell me how to change the timezone part without shifting the hours?

"I have a timestamp field in my table that has stored the data in UTC-10." PostgreSQL doesn't store timestamps that way. If you declared the column to be of type timestamp with time zone, "the internally stored value is always in UTC". — Mike Sherrill 'Cat Recall'
– Mike Sherrill 'Cat Recall', Commented Oct 27, 2014 at 23:36
Please, provide: CREATE TABLE for your table (timestamp column in particular), sample data found in the table and the result that you'd like to achieve. — vyegorov
– vyegorov, Commented Oct 28, 2014 at 7:04

Kirk Roybal · Accepted Answer · 2014-10-28 14:16:34Z

5

Strip off the time zone without changing the time, then add a timezone without converting the time:

(myfield::timestamp || 'UTC+10')::timestamptz

Select it again for your timezone to see that's what you wanted:

SELECT myfield AT TIME ZONE 'UTC+10'

edited Oct 28, 2014 at 14:16

answered Oct 27, 2014 at 22:40

Kirk Roybal

18k2 gold badges34 silver badges39 bronze badges

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

1 Comment

Mike Sherrill 'Cat Recall' Over a year ago

"ERROR: invalid input syntax . . ."

Collectives™ on Stack Overflow

postgresql: Change timestamp timezone part without shifting hours?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related