Postgres Pro
Downloads
Home   >   mailing lists

Thread: Convert from unixtime?

Convert from unixtime?

From
Forjan Tamas
Date:
Greetings.

I have some data files that I need to put into a database. My problem is
that all date fields are in unix time format (seconds since 1970.01.01
0:00). What I would like to find is a function that could be used in
INSERT commands to convert such data into a date format understandable to
Postgres. Any ideas?

Thank you very much, (please cc: me directly when answering)

--
Tamas Forjan
tech support
2F 2000 Kft.

Re: Convert from unixtime?

From
Bruno Wolff III
Date:
On Wed, Mar 19, 2003 at 20:07:14 +0100,
  Forjan Tamas <tamas@2fkft.com> wrote:
> Greetings.
>
> I have some data files that I need to put into a database. My problem is
> that all date fields are in unix time format (seconds since 1970.01.01
> 0:00). What I would like to find is a function that could be used in
> INSERT commands to convert such data into a date format understandable to
> Postgres. Any ideas?

You can do something like this (in 7.3):

select 1::abstime::timestamp with time zone;
or
select 1::abstime::timestamp with time zone at time zone 'GMT'::timestamp;

Depending on whether or not you want a time zone associated with the
timestamp. Note that if you go directly from abstime to timestamp
(without time zone), then the time will be off by your offset from GMT.

Re: Convert from unixtime?

From
Tom Lane
Date:
Bruno Wolff III <bruno@wolff.to> writes:
> select 1::abstime::timestamp with time zone;
> or
> select 1::abstime::timestamp with time zone at time zone 'GMT'::timestamp;

> Depending on whether or not you want a time zone associated with the
> timestamp. Note that if you go directly from abstime to timestamp
> (without time zone), then the time will be off by your offset from GMT.

It seems to me that that case also produces sensible results:

regression=# select 1::abstime::timestamp without time zone ;
      timestamp
---------------------
 1969-12-31 19:00:01
(1 row)

This corresponds to my local time (EST) at unix timestamp 1, which is
exactly what timestamp without time zone ought to show.

Obviously, which of these you want to use depends on what your goal is,
but they all give useful behaviors IMHO.

            regards, tom lane