Thread

1. converting seconds since epoch to time string

   Bill Morrow <wmorrow@home.com> — 2000-11-14T23:04:20Z

   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
   
   

2. Re: converting seconds since epoch to time string

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-14T23:23:53Z

   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
   
   

3. Re: converting seconds since epoch to time string

   ghaverla@freenet.edmonton.ab.ca — 2000-11-15T01:48:47Z

   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)