PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone
NickName:saicheg Ask DateTime:2014-01-22T15:55:04

PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

I faced with the following issue this morning:

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT';

returns me 2011-12-30 05:30:00+00 witch is wrong.

But next queries below:

select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'UTC-5';
select '2011-12-30 00:30:00' AT TIME ZONE 'EST5EDT';

i see right date 2011-12-29 19:30:00

Preventing your question about my local timezone:

SELECT  current_setting('TIMEZONE');
current_setting
-----------------
     UTC
(1 row)

Do anyone have answer why postgresql converts timestamp without time zone some weird way and instead taking away 5 hours it adds instead?

Copyright Notice:Content Author:「saicheg」,Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/21277170/postgresql-wrong-converting-from-timestamp-without-time-zone-to-timestamp-with-t

More about “PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone” related questions

PostgreSQL wrong converting from timestamp without time zone to timestamp with time zone

I faced with the following issue this morning: select '2011-12-30 00:30:00'::timestamp without time zone AT TIME ZONE 'EST5EDT'; returns me 2011-12-30 05:30:00+00 witch is wrong. But next queries

Show Detail

Timestamp without time zone error in PostgreSQL

While converting oracle to PostgreSQL I'm getting error like function trunc(timestamp without time zone) does not exist I have tried for both clock_timestamp(),current_timestamp() both not working.

Show Detail

PostgreSQL Parameter error (timestamp with time zone, timestamp without time zone)

I get an error Caused by: org.postgresql.util.PSQLException: ERROR: function months_between(timestamp with time zone, timestamp without time zone) does not exist Hint: No function matches the given

Show Detail

PostgreSQL Parameter error (timestamp with time zone, timestamp without time zone)

I get an error Caused by: org.postgresql.util.PSQLException: ERROR: function months_between(timestamp with time zone, timestamp without time zone) does not exist Hint: No function matches the given

Show Detail

Change column type from timestamp WITHOUT time zone to timestamp WITH time zone

I found this ALTER COLUMN statement in the PostgreSQL 9.3 ALTER TABLE manual page: ALTER TABLE audits ALTER COLUMN created_at SET DATA TYPE timestamp with time zone USING timestamp...

Show Detail

PostgreSQL: operator does not exist: timestamp without time zone == timestamp without time zone

I have created below trigger and function for update on function where I want to exclude trigger execution when there are update on two columns 'frm' and 'upto' which are of type "timestamp withou...

Show Detail

PostgreSQL alter type timestamp without time zone -> with time zone

The question is short: if I already have data in a column type timestamp without time zone, if I set the type to timestamp with time zone, what does postgresql do with this data?

Show Detail

How to convert a `timestamp without time zone` in a `timestamp with time zone` without getting the ouput in local time zone?

Context: I have a column of type timestamp without time zone containing timestamps recorded by a datalogger in UTC time. When I import this column in R, the time part is lost (see this SO post on t...

Show Detail

Postgres: meaning of `timestamp without time zone at time zone`

I am having some trouble understanding how to deal in Postgres with time zone semantic: Consider this table TABLE MyTable ( MyDate TIMESTAMP NOT NULL, ...

Show Detail

Converting timestamp to timestamp in a specific time zone in Postgres

I have a table that has a column whose data type is timestamp without time zone , I'm trying to convert it to timestamp without time zone at a given time zone. These are my Postgres 9.3 settings ...

Show Detail