extract and time zones

Hi there,

Regarding my last email. I have found the issue. Windows 2K is reporting
the timezine as GMTDT (GMT, Dublin time... I think) and I don't believe
Postgresql can understand this.

See the following code segments:

mcauleyt=# select timeofday();
timeofday
---------------------------------------
Fri Jul 25 10:38:13.056614 2003 GMTST
(1 row)

mcauleyt=# select substr(timeofday(), 1, 35);
substr
-------------------------------------
Fri Jul 25 10:38:16.952614 2003 GMT
(1 row)

mcauleyt=# select timeofday()::timestamp;
ERROR: Bad timestamp external representation 'Fri Jul 25
10:38:51.410614 2003 GMTST'

mcauleyt=# select substr(timeofday(), 1, 35)::timestamp;
substr
----------------------------
2003-07-25 10:38:57.918614
(1 row)

Um, and what about this?

$ uname -a
FreeBSD caneli 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Fri May 9 02:30:11 CEST 2003 root(at)caneli:/usr/obj/usr/src/sys/CANELI i386

$ psql --version
psql (PostgreSQL) 7.2.2
contains support for: readline, history, multibyte
Portions Copyright (c) 1996-2001, 토토 사이트
Portions Copyright (c) 1996, Regents of the University of California
Read the file COPYRIGHT or use the command \copyright to see the
usage and distribution terms.

create table t1( d timestamp(0) without time zone );
CREATE

insert into t1 values( current_timestamp );
INSERT 26251 1

select * from t1;
d
---------------------
2003-08-01 13:30:38
(1 row)

select extract( epoch from d ) from t1;
date_part
------------
1059744638
(1 row)

select extract( epoch from d::timestamp ) from t1;
date_part
------------
1059737438
(1 row)

d : 1059744638
d::timestamp : 1059737438

??

Does 'epoch from d' check time zone and 'epoch from d::timestamp' doesn't?

--

________________________________________________________
One ping to rule them all, one ping to find them,
one ping to bring them all and in the darkness bind them.
(Lord of the windows)

============================================================================
This e-mail message and any attached files are intended SOLELY for the addressee/s identified herein. It may contain CONFIDENTIAL and/or LEGALLY PRIVILEGED information and may not necessarily represent the opinion of this company. If you receive this message in ERROR, please immediately notify the sender and DELETE it since you ARE NOT AUTHORIZED to use, disclose, distribute, print or copy all or part of the contained information. Thank you.
============================================================================

Tim McAuley <mcauleyt(at)tcd(dot)ie> writes:
> Regarding my last email. I have found the issue. Windows 2K is reporting
> the timezine as GMTDT (GMT, Dublin time... I think) and I don't believe
> Postgresql can understand this.

No, it wouldn't recognize that. You could add the zone name as a
keyword in the datetktbl[] table, src/backend/utils/adt/datetime.c.

regards, tom lane

Oscar Estevez Lopez <oestevez(at)prisacom(dot)com> writes:
> d : 1059744638
> d::timestamp : 1059737438
> ??

This is fixed as of PG 7.3.4 ...

2003-02-27 16:37 tgl

* src/backend/utils/adt/timestamp.c (REL7_3_STABLE): Change
EXTRACT(EPOCH FROM timestamp) so that a timestamp without time zone
is assumed to be in local time, not GMT. This improves consistency
with other operations, which all assume local timezone when it
matters. Per bug #897.

regards, tom lane

Don't know the answer to your question, but how did you type all those commands in one second? :-)

Oscar Estevez Lopez wrote:

> Um, and what about this?
>
> $ uname -a
> FreeBSD caneli 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Fri May 9 02:30:11 CEST 2003 root(at)caneli:/usr/obj/usr/src/sys/CANELI i386
>
> $ psql --version
> psql (PostgreSQL) 7.2.2
> contains support for: readline, history, multibyte
> Portions Copyright (c) 1996-2001, 토토 사이트
> Portions Copyright (c) 1996, Regents of the University of California
> Read the file COPYRIGHT or use the command \copyright to see the
> usage and distribution terms.
>
> create table t1( d timestamp(0) without time zone );
> CREATE
>
> insert into t1 values( current_timestamp );
> INSERT 26251 1
>
>
> select * from t1;
> d
> ---------------------
> 2003-08-01 13:30:38
> (1 row)
>
> select extract( epoch from d ) from t1;
> date_part
> ------------
> 1059744638
> (1 row)
>
> select extract( epoch from d::timestamp ) from t1;
> date_part
> ------------
> 1059737438
> (1 row)
>
> d : 1059744638
> d::timestamp : 1059737438
>
> ??
>
> Does 'epoch from d' check time zone and 'epoch from d::timestamp' doesn't?
>
>
>
>
>

On Fri, 2003-08-01 at 10:19, Dennis Gearon wrote:
> Don't know the answer to your question, but how did you type all those commands in one second? :-)

From a script?

$ psql -a -f bar.sql template1
drop table bar;
DROP TABLE
create table bar (f1 timestamp);
CREATE TABLE
insert into bar values (current_timestamp);
INSERT 17010 1
insert into bar values (current_timestamp);
INSERT 17011 1
select extract(epoch from f1) from bar;
date_part
------------------
1059752510.67238
1059752510.69783
(2 rows)

> Oscar Estevez Lopez wrote:
>
> > Um, and what about this?
> >
> > $ uname -a
> > FreeBSD caneli 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Fri May 9 02:30:11 CEST 2003 root(at)caneli:/usr/obj/usr/src/sys/CANELI i386
> >
> > $ psql --version
> > psql (PostgreSQL) 7.2.2
> > contains support for: readline, history, multibyte
> > Portions Copyright (c) 1996-2001, 토토 사이트
> > Portions Copyright (c) 1996, Regents of the University of California
> > Read the file COPYRIGHT or use the command \copyright to see the
> > usage and distribution terms.
> >
> > create table t1( d timestamp(0) without time zone );
> > CREATE
> >
> > insert into t1 values( current_timestamp );
> > INSERT 26251 1
> >
> >
> > select * from t1;
> > d
> > ---------------------
> > 2003-08-01 13:30:38
> > (1 row)
> >
> > select extract( epoch from d ) from t1;
> > date_part
> > ------------
> > 1059744638
> > (1 row)
> >
> > select extract( epoch from d::timestamp ) from t1;
> > date_part
> > ------------
> > 1059737438
> > (1 row)
> >
> > d : 1059744638
> > d::timestamp : 1059737438
> >
> > ??
> >
> > Does 'epoch from d' check time zone and 'epoch from d::timestamp' doesn't?
> >
> >
> >
> >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
--
+-----------------------------------------------------------------+
| Ron Johnson, Jr. Home: ron(dot)l(dot)johnson(at)cox(dot)net |
| Jefferson, LA USA |
| |
| "I'm not a vegetarian because I love animals, I'm a vegetarian |
| because I hate vegetables!" |
| unknown |
+-----------------------------------------------------------------+

El Fri, 01 Aug 2003 08:19:25 -0700
Dennis Gearon <gearond(at)cvc(dot)net> escribio:

Cut'n paste :P

> Don't know the answer to your question, but how did you type all those commands in one second? :-)
>
> Oscar Estevez Lopez wrote:
>
> > Um, and what about this?
> >
> > $ uname -a
> > FreeBSD caneli 4.7-RELEASE FreeBSD 4.7-RELEASE #1: Fri May 9 02:30:11 CEST 2003 root(at)caneli:/usr/obj/usr/src/sys/CANELI i386
> >
> > $ psql --version
> > psql (PostgreSQL) 7.2.2
> > contains support for: readline, history, multibyte
> > Portions Copyright (c) 1996-2001, 토토 사이트
> > Portions Copyright (c) 1996, Regents of the University of California
> > Read the file COPYRIGHT or use the command \copyright to see the
> > usage and distribution terms.
> >
> > create table t1( d timestamp(0) without time zone );
> > CREATE
> >
> > insert into t1 values( current_timestamp );
> > INSERT 26251 1
> >
> >
> > select * from t1;
> > d
> > ---------------------
> > 2003-08-01 13:30:38
> > (1 row)
> >
> > select extract( epoch from d ) from t1;
> > date_part
> > ------------
> > 1059744638
> > (1 row)
> >
> > select extract( epoch from d::timestamp ) from t1;
> > date_part
> > ------------
> > 1059737438
> > (1 row)
> >
> > d : 1059744638
> > d::timestamp : 1059737438
> >
> > ??
> >
> > Does 'epoch from d' check time zone and 'epoch from d::timestamp' doesn't?
> >
> >
> >
> >
> >
>

--

________________________________________________________
One ping to rule them all, one ping to find them,
one ping to bring them all and in the darkness bind them.
(Lord of the windows)

============================================================================
This e-mail message and any attached files are intended SOLELY for the addressee/s identified herein. It may contain CONFIDENTIAL and/or LEGALLY PRIVILEGED information and may not necessarily represent the opinion of this company. If you receive this message in ERROR, please immediately notify the sender and DELETE it since you ARE NOT AUTHORIZED to use, disclose, distribute, print or copy all or part of the contained information. Thank you.
============================================================================

El Fri, 01 Aug 2003 10:55:49 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escribio:

thanks :)

> Oscar Estevez Lopez <oestevez(at)prisacom(dot)com> writes:
> > d : 1059744638
> > d::timestamp : 1059737438
> > ??
>
> This is fixed as of PG 7.3.4 ...
>
> 2003-02-27 16:37 tgl
>
> * src/backend/utils/adt/timestamp.c (REL7_3_STABLE): Change
> EXTRACT(EPOCH FROM timestamp) so that a timestamp without time zone
> is assumed to be in local time, not GMT. This improves consistency
> with other operations, which all assume local timezone when it
> matters. Per bug #897.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--

________________________________________________________
One ping to rule them all, one ping to find them,
one ping to bring them all and in the darkness bind them.
(Lord of the windows)

============================================================================
This e-mail message and any attached files are intended SOLELY for the addressee/s identified herein. It may contain CONFIDENTIAL and/or LEGALLY PRIVILEGED information and may not necessarily represent the opinion of this company. If you receive this message in ERROR, please immediately notify the sender and DELETE it since you ARE NOT AUTHORIZED to use, disclose, distribute, print or copy all or part of the contained information. Thank you.
============================================================================