Why date time different 1 hour when retrieve timestamp data of postgresql from rails

Question

I have a problem that is I migrate database table from rails. In the tables columns timestamps with timezone is also include.

When I insert data into database, timestamp columns save current time as (eg. 2012-08-09 12:00:00 UTC+6:30)

I think it means 2012-08-09 18:30. but when i retrieve this data from rails 3.2.6 it display as 2012-08-09 5:30

Why it display as 1 hour different from actual time. Is there any idea? please

Sorry I updated 2012-08-09 6:30 to 2012-08-09 18:30 in actual time. — Nay Zaw, Aug 13 '12 at 02:32
If any of the answers were able to help you. Please accept one. — Bharat Sinha, Aug 13 '12 at 04:47
... and if the answers don't help, please consider posting your own with an explanation of what was happening and how you fixed it, to help other people who find this question in searches later. — Craig Ringer, Aug 13 '12 at 06:37

Bharat Sinha · Answer 1 · 2012-08-12T07:53:15.003

1

the time

2012-08-09 12:00:00 UTC+6:30

is in the timezone UTC+6:30(GMT+6:30) when you save it.

When you retrieve it you get the time in GMT timezone.

Both are same timings as 2012-08-09 5:30 GMT = 12:00:00 GMT+6:30.

EDIT:

This is for @CraigRinger...

Let's say...

UTC is London and UTC + 6:30 is Rangoon so... 

6:30 at Rangoon = 12:00 at London
                         Subtracting 6:30 from both    
12:00 at Rangoon = 5:30 at London
12:00 UTC+6:30 = 5:30 UTC
Data stored in UTC+6:30 = Data retrieved in UTC

edited Aug 12 '12 at 07:53

answered Aug 11 '12 at 11:24

Bharat Sinha

13,973
6
39
63

Um, what? No, `UTC` is the new name for `GMT`. They are the same time zone. Run `SELECT TIMESTAMP WITH TIME ZONE '2012-01-01 00:00:00 UTC' = TIMESTAMP WITH TIME ZONE '2012-01-01 00:00:00 GMT';` . I'm sure you're right that time zones are involved, but I'd be more suspicious about daylight savings time given the 1 hour gap. – Craig Ringer Aug 12 '12 at 05:20
Also, converting your assertion to an equality test shows it's false: `SELECT TIMESTAMPTZ '2012-08-09 5:30 GMT' = TIMESTAMPTZ '2012-08-09 12:00:00 UTC+6:30';` – Craig Ringer Aug 12 '12 at 05:34
@CraigRinger even if UTC and GMT are same when the data is stored it's GMT+6:30 timezone and when retrieved it's GMT. Hope that makes sense. – Bharat Sinha Aug 12 '12 at 06:45
@BaharatSinha Not convinced. `SELECT TIMESTAMPTZ '2012-08-09 12:00:00 UTC+6:30' AT TIME ZONE 'GMT';` is 6:30 GMT not 5:30 as the user reports seeing. Where's the hour difference between +6:30 time and the 5:30 result coming from? – Craig Ringer Aug 12 '12 at 07:43
1

@BaharatSinha OK, that makes sense. Thanks for elaborating. I found it hard to follow your reasoning initially. – Craig Ringer Aug 12 '12 at 11:42
Thanks for acceptance... My bad If my wordings were not clear initially. – Bharat Sinha Aug 13 '12 at 04:46
No problems. I was thrown by the use of 12 hour time, I forget it exists ;-) – Craig Ringer Aug 13 '12 at 06:36

Craig Ringer · Answer 2 · 2012-08-12T05:37:36.573

At a guess, you're storing it with one timezone setting and retrieving it with a different one.

For example, if Rails thinks you're in UTC+6:30 but PostgreSQL thinks you're in UTC+5:30, and if Rails sends dates to Pg timezone qualified but reads them from Pg with the assumption that they're in local time, this would happen. It's safest to make sure your database driver always reads and writes dates timezone-qualified.

Given the one hour gap, I'm wondering if daylight savings is involved, but it could also just be that your timezone is off by one hour.

regress=# create table test ( x timestamp with time zone );
CREATE TABLE
regress=# insert into test (x) values ('2012-08-09 12:00:00 UTC+6:30');
INSERT 0 1
regress=# SET TIMEZONE = '-5:30';
SET
regress=# select * from test;
             x             
---------------------------
 2012-08-10 00:00:00+05:30
(1 row)

regress=# SET TIMEZONE = '-6:30';
SET
regress=# select * from test;
             x             
---------------------------
 2012-08-10 01:00:00+06:30
(1 row)

Alternately, maybe your database is in time zone '-1:00' and your application is stripping off the time zone when it reads the date, so the date appears to be off by one hour. It's hard to say with the available information.

To really help you it would be necessary for you to show:

The code that inserts the date
The INSERT statement that really inserts the date, captured by enabling log_statement = 'all' in postgresql.conf, along with any SET TIMEZONE statements that session ran before the INSERT.
The result of SELECTing that column from the database after a SET TIMEZONE = 'UTC'
and the code that reads the date

Why date time different 1 hour when retrieve timestamp data of postgresql from rails

2 Answers2

EDIT: