Thread: to_timestamp overloaded to convert from Unix epoch
Please find attached a patch (diff -c against cvs HEAD) to add a
function that accepts a double precision argument assumed to be a Unix
epoch timestamp and returns timestamp with time zone, and accompanying
documentation.
Usage:
test=# select to_timestamp(200120400);
to_timestamp
------------------------
1976-05-05 14:00:00+09
(1 row)
If regression tests are required, I will produce some. I'd appreciate
any pointers as to what to look for, as they would be my first attempt
at writing regression tests.
Regards
Michael Glaesemann
grzm myrealbox com
Attachment
I wonder... Maybe you don't need to override to_timestamp, and you can just add a new code to the format string that to_timestamp understands, ie 'e' or something or 'u' that means 'unix timestamp'? Chris Michael Glaesemann wrote: > Please find attached a patch (diff -c against cvs HEAD) to add a > function that accepts a double precision argument assumed to be a Unix > epoch timestamp and returns timestamp with time zone, and accompanying > documentation. > > Usage: > > test=# select to_timestamp(200120400); > to_timestamp > ------------------------ > 1976-05-05 14:00:00+09 > (1 row) > > If regression tests are required, I will produce some. I'd appreciate > any pointers as to what to look for, as they would be my first attempt > at writing regression tests. > > Regards > > Michael Glaesemann > grzm myrealbox com > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Aug 14, 2004, at 6:50 PM, Christopher Kings-Lynne wrote: > Maybe you don't need to override to_timestamp, and you can just add a > new code to the format string that to_timestamp understands, ie 'e' or > something or 'u' that means 'unix timestamp'? Thanks for looking at the code! I know people are really busy now with 8.0.0beta1. Are you thinking something like to_timestamp(200120400,'unix')? One thing that I was thinking about when looking at the docs is that the other to_ functions (other than to_char) took first a text-based argument, and then the format. This to_timestamp takes (or casts to) a double. I can see how it might be forward-looking to have two parameters in case we wanted to convert from some other numeric-based timestamp to timestamptz; just change the formatting parameter. Two things come time mind: (1) I can't think of an instance when someone has asked to convert from some other numeric-based timestamp. (2) If the day comes when we would want to add another conversion, nothing prevents us from creating it (though retaining the one-parameter version for backwards-compatibility might be a reason against this). Thoughts? Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> On Aug 14, 2004, at 6:50 PM, Christopher Kings-Lynne wrote:
>> Maybe you don't need to override to_timestamp, and you can just add a
>> new code to the format string that to_timestamp understands, ie 'e' or
>> something or 'u' that means 'unix timestamp'?
> Are you thinking something like to_timestamp(200120400,'unix')?
I didn't see the point of that suggestion either. People who want to do
this conversion usually want to start with a number, not a string, so
it wouldn't be part of the existing to_timestamp function anyway.
There was however another patch submitted recently that seemed to
duplicate yours functionally but used a different syntax --- I think the
guy had started by looking at extract(epoch from timestamp) rather than
to_timestamp.
regards, tom lane
On Aug 15, 2004, at 1:19 AM, Tom Lane wrote: > There was however another patch submitted recently that seemed to > duplicate yours functionally but used a different syntax --- I think > the > guy had started by looking at extract(epoch from timestamp) rather than > to_timestamp. Other than Chris' suggestion of extract(timestamp from epoch)? I did find this documentation patch from December 2003 giving an example of how to convert from Unix epoch to timestamp, but not a function per se. http://archives.postgresql.org/pgsql-patches/2003-12/msg00112.php However, I suspect you may thinking of something else. Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> On Aug 15, 2004, at 1:19 AM, Tom Lane wrote:
>> There was however another patch submitted recently that seemed to
>> duplicate yours functionally but used a different syntax --- I think
> Other than Chris' suggestion of extract(timestamp from epoch)?
[ looks in archives... ] Oh, actually that was *you* --- I was vaguely
remembering your proposed patch of 2-Aug. You were calling the function
epoch_to_timestamp at the time.
I like to_timestamp(double) better than these other names ...
regards, tom lane
TODO here? --------------------------------------------------------------------------- Tom Lane wrote: > Michael Glaesemann <grzm@myrealbox.com> writes: > > On Aug 15, 2004, at 1:19 AM, Tom Lane wrote: > >> There was however another patch submitted recently that seemed to > >> duplicate yours functionally but used a different syntax --- I think > > > Other than Chris' suggestion of extract(timestamp from epoch)? > > [ looks in archives... ] Oh, actually that was *you* --- I was vaguely > remembering your proposed patch of 2-Aug. You were calling the function > epoch_to_timestamp at the time. > > I like to_timestamp(double) better than these other names ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- 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
Sorry, I now see I should add the patch to the 8.1 queue. --------------------------------------------------------------------------- Bruce Momjian wrote: > > TODO here? > > --------------------------------------------------------------------------- > > Tom Lane wrote: > > Michael Glaesemann <grzm@myrealbox.com> writes: > > > On Aug 15, 2004, at 1:19 AM, Tom Lane wrote: > > >> There was however another patch submitted recently that seemed to > > >> duplicate yours functionally but used a different syntax --- I think > > > > > Other than Chris' suggestion of extract(timestamp from epoch)? > > > > [ looks in archives... ] Oh, actually that was *you* --- I was vaguely > > remembering your proposed patch of 2-Aug. You were calling the function > > epoch_to_timestamp at the time. > > > > I like to_timestamp(double) better than these other names ... > > > > regards, tom lane > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > > -- > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- 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
This has been saved for the 8.1 release:
http:/momjian.postgresql.org/cgi-bin/pgpatches2
---------------------------------------------------------------------------
Michael Glaesemann wrote:
> Please find attached a patch (diff -c against cvs HEAD) to add a
> function that accepts a double precision argument assumed to be a Unix
> epoch timestamp and returns timestamp with time zone, and accompanying
> documentation.
>
> Usage:
>
> test=# select to_timestamp(200120400);
> to_timestamp
> ------------------------
> 1976-05-05 14:00:00+09
> (1 row)
>
> If regression tests are required, I will produce some. I'd appreciate
> any pointers as to what to look for, as they would be my first attempt
> at writing regression tests.
>
> Regards
>
> Michael Glaesemann
> grzm myrealbox com
>
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
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
Updated patch attached with both documentation portions --- applied. --------------------------------------------------------------------------- Michael Glaesemann wrote: > Please find attached a patch (diff -c against cvs HEAD) to add a > function that accepts a double precision argument assumed to be a Unix > epoch timestamp and returns timestamp with time zone, and accompanying > documentation. > > Usage: > > test=# select to_timestamp(200120400); > to_timestamp > ------------------------ > 1976-05-05 14:00:00+09 > (1 row) > > If regression tests are required, I will produce some. I'd appreciate > any pointers as to what to look for, as they would be my first attempt > at writing regression tests. > > Regards > > Michael Glaesemann > grzm myrealbox com > [ Attachment, skipping... ] > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- 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:33:38 -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> *************** *** 4139,4144 **** --- 4146,4157 ---- <entry><literal>to_timestamp('05 Dec 2000', 'DD Mon YYYY')</literal></entry> </row> <row> + <entry><literal><function>to_timestamp</function>(<type>double precision</type>)</literal></entry> + <entry><type>timestamp with time zone</type></entry> + <entry>convert UNIX epoch to time stamp</entry> + <entry><literal>to_timestamp(200120400)</literal></entry> + </row> + <row> <entry><literal><function>to_number</function>(<type>text</type>, <type>text</type>)</literal></entry> <entry><type>numeric</type></entry> <entry>convert string to numeric</entry> 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:33:45 -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");