0

So I'm storing a timezone aware time, lets say 12 midday in Moscow as 2014-01-22 12:00:00+04. I want to return that, but if I just call select my_timestamp from my_table it returns 2014-01-22 08:00:00+00. I have a timezone column available with the timezone's olson name (in this case europe/moscow), is there any way I can construct a select that returns me the exact object I input with its timezone preserved, as opposed to it converted into my server's local time (GMT)? I've had a look at the postgres docs that mention the at time zone function but haven't had any success getting it to keep time zone info when returning.

bbm
  • 130
  • 1
  • 1
  • 7
  • Erwin Brandstetter gave a great answer a while back that you might find useful: http://stackoverflow.com/a/9576170 – bma Jan 22 '14 at 15:32
  • Thanks bma, that's a pretty comprehensive breakdown. A shame, I guess I'll have to store the time as naive and combine with my timezone in my app's code after all. – bbm Jan 22 '14 at 17:27

2 Answers2

1

No, there's no way to do that. PostgreSQL doesn't store the offset of the client that inserts or updates the value.

If you store that offset in a separate column, you can concatenate it with the timestamp value, but you run the risk of returning the wrong value for certain conditions that involve daylight savings time.

Using the AT TIME ZONE syntax along with the right time zone should give you the right timestamp value, but it won't include the offset of the client that originally inserted or updated the timestamp.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
0

See if this helps in anyway:

SELECT now(), now()::timestamp, now() AT TIME ZONE 'europe/moscow', now()::timestamp AT TIME ZONE 'europe/moscow'

You can convert between timezones. Does not give you the timezone+04 but gives you the time at that timezone.

Bruno
  • 623
  • 4
  • 6