Thread: (8.1) to_timestamp correction (epoch to timestamptz)
Note: This patch is intended for 8.1 (as was the original).
I believe the previous patch I submitted to convert Unix epoch to
timestamptz contains a bug relating to its use of AT TIME ZONE. Please
find attached a corrected patch diffed against HEAD, which includes
documentation.
The original function was equivalent to
CREATE FUNCTION to_timestamp (DOUBLE PRECISION)
RETURNS timestamptz
LANGUAGE SQL AS '
select (
(\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
at time zone \'UTC\'
)
';
The AT TIME ZONE 'UTC' removes the time zone from the timestamptz,
returning timestamp. However, the function is declared to return
timestamptz. The original patch appeared to work, but creating this
equivalent function fails as it doesn't return the declared datatype.
The corrected function restores the time zone with an additional AT
TIME ZONE 'UTC':
CREATE FUNCTION to_timestamp (double precision)
returns timestamptz
language sql as '
select (
(\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
at time zone \'UTC\'
) at time zone \'UTC\'
';
Michael Glaesemann
grzm myrealbox com
Attachment
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Michael Glaesemann wrote:
> Note: This patch is intended for 8.1 (as was the original).
>
> I believe the previous patch I submitted to convert Unix epoch to
> timestamptz contains a bug relating to its use of AT TIME ZONE. Please
> find attached a corrected patch diffed against HEAD, which includes
> documentation.
>
> The original function was equivalent to
>
> CREATE FUNCTION to_timestamp (DOUBLE PRECISION)
> RETURNS timestamptz
> LANGUAGE SQL AS '
> select (
> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
> at time zone \'UTC\'
> )
> ';
>
> The AT TIME ZONE 'UTC' removes the time zone from the timestamptz,
> returning timestamp. However, the function is declared to return
> timestamptz. The original patch appeared to work, but creating this
> equivalent function fails as it doesn't return the declared datatype.
>
> The corrected function restores the time zone with an additional AT
> TIME ZONE 'UTC':
>
> CREATE FUNCTION to_timestamp (double precision)
> returns timestamptz
> language sql as '
> select (
> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
> at time zone \'UTC\'
> ) at time zone \'UTC\'
> ';
>
>
> Michael Glaesemann
> grzm myrealbox com
>
[ Attachment, skipping... ]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce, Please note that this patch is a correction and replacement for an earlier patch in the queue. The patch accompanying the message http://candle.pha.pa.us/mhonarc/patches/msg00008.html should be removed from the queue and not applied. The one (originally) attached to this message should be applied. Thanks! Michael Glaesemann grzm myrealbox com On Jun 5, 2005, at 9:17 AM, Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://momjian.postgresql.org/cgi-bin/pgpatches > > It will be applied as soon as one of the PostgreSQL committers reviews > and approves it. > > ---------------------------------------------------------------------- > ----- > > > Michael Glaesemann wrote: > >> Note: This patch is intended for 8.1 (as was the original). >> >> I believe the previous patch I submitted to convert Unix epoch to >> timestamptz contains a bug relating to its use of AT TIME ZONE. >> Please >> find attached a corrected patch diffed against HEAD, which includes >> documentation. >> >> The original function was equivalent to >> >> CREATE FUNCTION to_timestamp (DOUBLE PRECISION) >> RETURNS timestamptz >> LANGUAGE SQL AS ' >> select ( >> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) >> at time zone \'UTC\' >> ) >> '; >> >> The AT TIME ZONE 'UTC' removes the time zone from the timestamptz, >> returning timestamp. However, the function is declared to return >> timestamptz. The original patch appeared to work, but creating this >> equivalent function fails as it doesn't return the declared datatype. >> >> The corrected function restores the time zone with an additional AT >> TIME ZONE 'UTC': >> >> CREATE FUNCTION to_timestamp (double precision) >> returns timestamptz >> language sql as ' >> select ( >> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval) >> at time zone \'UTC\' >> ) at time zone \'UTC\' >> '; >> >> >> Michael Glaesemann >> grzm myrealbox com >> >> > > [ Attachment, skipping... ]
Michael Glaesemann wrote: > Bruce, > > Please note that this patch is a correction and replacement for an > earlier patch in the queue. The patch accompanying the message > http://candle.pha.pa.us/mhonarc/patches/msg00008.html > should be removed from the queue and not applied. > > The one (originally) attached to this message should be applied. Right, the one to be applied is: r 17 Dec 12 Michael Glaesemann (183) [PATCHES] (8.1) to_timestamp correction (epoch to timestamptz) The earlier one is in the queue because it has an example of usage. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
I have modified your original patch and applied it. Tom mentioned to me
privately that none of the AT TIME ZONE clauses is required, probably
because epoch is already UTC, and adding seconds to it just keeps it
UTC, then it is converted to your local timezone for display. I also
found your patch was lacking two _null_ columns that are now needed
because of pg_proc column additions.
Testing shows it works:
$ date '+%s'
1118333328
test=> select to_timestamp(1118333328);
to_timestamp
------------------------
2005-06-09 12:08:48-04
(1 row)
test=> select current_timestamp;
timestamptz
-------------------------------
2005-06-09 12:09:01.746045-04
(1 row)
Applied patch attached.
---------------------------------------------------------------------------
Michael Glaesemann wrote:
> Note: This patch is intended for 8.1 (as was the original).
>
> I believe the previous patch I submitted to convert Unix epoch to
> timestamptz contains a bug relating to its use of AT TIME ZONE. Please
> find attached a corrected patch diffed against HEAD, which includes
> documentation.
>
> The original function was equivalent to
>
> CREATE FUNCTION to_timestamp (DOUBLE PRECISION)
> RETURNS timestamptz
> LANGUAGE SQL AS '
> select (
> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
> at time zone \'UTC\'
> )
> ';
>
> The AT TIME ZONE 'UTC' removes the time zone from the timestamptz,
> returning timestamp. However, the function is declared to return
> timestamptz. The original patch appeared to work, but creating this
> equivalent function fails as it doesn't return the declared datatype.
>
> The corrected function restores the time zone with an additional AT
> TIME ZONE 'UTC':
>
> CREATE FUNCTION to_timestamp (double precision)
> returns timestamptz
> language sql as '
> select (
> (\'epoch\'::timestamptz + $1 * \'1 second\'::interval)
> at time zone \'UTC\'
> ) at time zone \'UTC\'
> ';
>
>
> Michael Glaesemann
> grzm myrealbox com
>
[ Attachment, skipping... ]
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.252
diff -c -c -r1.252 func.sgml
*** doc/src/sgml/func.sgml 7 Jun 2005 07:08:34 -0000 1.252
--- doc/src/sgml/func.sgml 9 Jun 2005 16:20:35 -0000
***************
*** 4082,4087 ****
--- 4082,4094 ----
argument is the value to be formatted and the second argument is a
template that defines the output or input format.
</para>
+ <para>
+ The <function>to_timestamp</function> function can also take a single
+ <type>double precision</type> argument to convert from Unix epoch to
+ <type>timestamp with time zone</type>.
+ (<type>Integer</type> Unix epochs are implicitly cast to
+ <type>double precision</type>.)
+ </para>
<table id="functions-formatting-table">
<title>Formatting Functions</title>
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.364
diff -c -c -r1.364 pg_proc.h
*** src/include/catalog/pg_proc.h 7 Jun 2005 07:08:34 -0000 1.364
--- src/include/catalog/pg_proc.h 9 Jun 2005 16:20:44 -0000
***************
*** 1459,1464 ****
--- 1459,1466 ----
DESCR("greater-than-or-equal");
DATA(insert OID = 1157 ( timestamptz_gt PGNSP PGUID 12 f f t f i 2 16 "1184 1184" _null_ _null_ _null_
timestamp_gt- _null_ ));
DESCR("greater-than");
+ DATA(insert OID = 1158 ( to_timestamp PGNSP PGUID 14 f f t f i 1 1184 "701" _null_ _null_ _null_ "select
(\'epoch\'::timestamptz+ $1 * \'1 second\'::interval)" - _null_ ));
+ DESCR("convert UNIX epoch to timestamptz");
DATA(insert OID = 1159 ( timezone PGNSP PGUID 12 f f t f i 2 1114 "25 1184" _null_ _null_ _null_
timestamptz_zone- _null_ ));
DESCR("adjust timestamp to new time zone");
On Jun 10, 2005, at 1:29 AM, Bruce Momjian wrote: > I have modified your original patch and applied it. Tom mentioned > to me > privately that none of the AT TIME ZONE clauses is required, probably > because epoch is already UTC, and adding seconds to it just keeps it > UTC, then it is converted to your local timezone for display. I also > found your patch was lacking two _null_ columns that are now needed > because of pg_proc column additions. Thank, Tom and Bruce, for fixing and applying my patch. Michael Glaesemann grzm myrealbox com