Thread: converting seconds since epoch to time string

I have an integer column which holds a time represented
as the number of seconds since 1970/01/01 00:00 UTC. I want to
view this table with the time converted to an understandable text string.

I've experimented with things like

select to_char('epoch'::datetime + '973660200 seconds'::interval, 'dd/mm/yyyy hh:mi');
     to_char
------------------
 07/11/2000 10:10

but can't see anyway to use the time column in my table, instead of a hardcode
number of seconds.

In Sybase, you can use the dateadd() function to do this:

select dateadd( seconds, time, '1/1/70') from tablename

Thanks,
Bill

Bill Morrow <wmorrow@home.com> writes:
> I have an integer column which holds a time represented
> as the number of seconds since 1970/01/01 00:00 UTC. I want to
> view this table with the time converted to an understandable text string.

Casting to abstime should do it; from there you might want to convert
to timestamp so you can invoke to_char, if you don't like the default
display format.

Next time, try representing the column as a date/time datatype in the
first place ;-)

            regards, tom lane

On Tue, 14 Nov 2000, Bill Morrow wrote:

> I have an integer column which holds a time represented
> as the number of seconds since 1970/01/01 00:00 UTC. I want to
> view this table with the time converted to an understandable text string.

If perl programming is remotely involved at any point in this,
I heartedly recommend you view Date::Manip, the swiss-army
chainsaw of Date/Time manipulations.  The only thing it seems
to barf on is character time zone data.  There are too many
time zones which have the same abbreviations.

Gord

Matter Realisations     http://www.materialisations.com/
Gordon Haverland, B.Sc. M.Eng. President
101  9504 182 St. NW    Edmonton, AB, CA  T5T 3A7
780/481-8019            ghaverla @ freenet.edmonton.ab.ca
780/993-1274 (cell)