Thread: BUG #12460: Extracting epoch from timestamp string incorrectly assumes "with time zone"

The following bug has been logged on the website:

Bug reference:      12460
Logged by:          Alan Edel
Email address:      adeconsulting@pacbell.net
PostgreSQL version: 9.3.5
Operating system:   FreeBSD 9.3
Description:

8.3.6 queries using "timestamp" and "timestamptz" casting both give the same
result:

select extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
 date_part
------------
 1420668000
(1 row)

select extract(epoch from cast('2015/01/07 14:00:00' as timestamptz));
 date_part
------------
 1420668000
(1 row)

However, 9.3.5 gives different results, the first one appears to be
incorrect:

select extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
 date_part
------------
 1420639200
(1 row)

select extract(epoch from cast('2015/01/07 14:00:00' as timestamptz));
 date_part
------------
 1420668000
(1 row)

adeconsulting wrote
> The following bug has been logged on the website:
>
> Bug reference:      12460
> Logged by:          Alan Edel
> Email address:

> adeconsulting@

> PostgreSQL version: 9.3.5
> Operating system:   FreeBSD 9.3
> Description:
>
> 8.3.6 queries using "timestamp" and "timestamptz" casting both give the
> same
> result:
>
> select extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
>  date_part
> ------------
>  1420668000
> (1 row)
>
> select extract(epoch from cast('2015/01/07 14:00:00' as timestamptz));
>  date_part
> ------------
>  1420668000
> (1 row)
>
> However, 9.3.5 gives different results, the first one appears to be
> incorrect:
>
> select extract(epoch from cast('2015/01/07 14:00:00' as timestamp));
>  date_part
> ------------
>  1420639200
> (1 row)
>
> select extract(epoch from cast('2015/01/07 14:00:00' as timestamptz));
>  date_part
> ------------
>  1420668000
> (1 row)

Intentional behavior change in 9.2

http://www.postgresql.org/docs/9.2/interactive/release-9-2.html

David J.



--
View this message in context:
http://postgresql.nabble.com/BUG-12460-Extracting-epoch-from-timestamp-string-incorrectly-assumes-with-time-zone-tp5833256p5833277.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.