postgresql numeric to timestamp conversion timezone issue

Question

I am trying to convert a numeric to timestamp in postgresql. However, it always converts it in EST timezone. Before running the query, I try the following.

set time zone 'UTC+10';

select to_timestamp(r.date/1000) as current_email_timestamp
FROM email;

However, It the timestamp always seem to be in US timezone. The emails are mostly sent during working hours but when I change the numeric back to timestamp with above query, it shows all the emails at night time

Could it be that the timestamp in numeric was stored in US timezone, or could it be that when converting back from numeric to timestamp, it is not coverting the timezone correctly.

Can someone please tell me how to fix this

Regards Arif

Can you show us what the input numerics look like?

Politank-Z
– Politank-Z

2014-10-22 18:21:39 +00:00
Commented Oct 22, 2014 at 18:21 — Politank-Z
– Politank-Z, Commented Oct 22, 2014 at 18:21

imran · Accepted Answer · 2014-10-22 18:28:56Z

2

You can use the at time zone modifier:

select to_timestamp(1411738200), 
       to_timestamp(1411738200) at time zone 'America/Chicago', 
       to_timestamp(1411738200) at time zone 'UTC+10'

Your postgres installation probably defaults to EST.

answered Oct 22, 2014 at 18:28

imran

1,56010 silver badges8 bronze badges

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

Collectives™ on Stack Overflow

postgresql numeric to timestamp conversion timezone issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related