postgresql: numeric to timestamp issue with timezone

Question

I have extracted some emails from gmail into postgresql. the dates are stored in numeric. some few sample dates are

1399586523000
1399773692000
1400191333000
1400378501000
1400796072000
1401008483000
1401400912000
1401588106000

when I convert them into timestamp

to_timestamp(date/1000) at time zone 'UTC+10' as senttimestamp

The Issue is that neither the timezone match nor the minutes part in the email match. I could have easily shifted the timezone forward or backward to adjust but the minute part of the date also does not match. I doubt that maybe I am incorrectly coverting from numeric to timestamp

For example, 1399586523000 timestamp should be Thu, May 8, 2014 at 4:21 PM but when I use the

to_timestamp(date/1000) at time zone 'UTC+10' as senttimestamp

None of the converted timestamp match the actual sent timestamp

Can someone please help

Arif

Daniel Vérité · Accepted Answer · 2014-10-25 17:49:29Z

1

For example, 1399586523000 timestamp should be Thu, May 8, 2014 at 4:21 PM

Assuming an UTC timestamp in number of milliseconds since 1/1/1970, the expected result is incorrect and PostgreSQL's result is correct.

Here's what Perl's localtime gives:

$ LANG=C TZ=GMT 
 perl -e 'use POSIX; print strftime("%c", localtime(1399586523));'

result: Thu May 8 22:02:03 2014

$ LANG=C TZ=UTC+10
  perl -e 'use POSIX; print strftime("%c", localtime(1399586523));'

result: Thu May 8 12:02:03 2014

PostgreSQL:

=> select to_timestamp(1399586523) at time zone 'UTC+10';
      timezone       
---------------------
 2014-05-08 12:02:03
(1 row)

Date in mail messages are expressed in RFC-822 format, not in Unix timestamps, so it' not obvious how your timestamps were obtained and what they represent exactly.

answered Oct 25, 2014 at 17:49

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

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

1 Comment

Mike Sherrill 'Cat Recall' Over a year ago

FWIW, the X-Received header seems to store a Unix timestamp as part of an SMTP id.

Collectives™ on Stack Overflow

postgresql: numeric to timestamp issue with timezone

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