to_timestamp overloaded to convert from Unix epoch

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:

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