Thread: Importing data w/ Unix timestamp

I want to copy in some data from a tab-delimited flat file, but one of the
columns that should translate into a datetime is in Unix timestamp format
(seconds since epoch).

The data should go into this table:
create table logins (
    uname varchar(100),
    timein datetime,
    duration int,
    cid varchar(20),
    natip varchar(20)
);

But it looks like this in the comma-delimited flatfile:

bob     957974401    17    8325824    210.16.68.250
james     957974401    13    3737270    210.16.68.250
edwin    957974402    201    3672136    210.16.68.250
steph    957974404    25    9398998    210.16.68.250
mitch    957974406    7    7221757    210.16.68.250

--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran
Work: (632)7182222       Home: (632)9227123

brianb-pggeneral@edsamail.com writes:

>
> I want to copy in some data from a tab-delimited flat file, but one of the
> columns that should translate into a datetime is in Unix timestamp format
> (seconds since epoch).
>
> The data should go into this table:
> create table logins (
>     uname varchar(100),
>     timein datetime,
>     duration int,
>     cid varchar(20),
>     natip varchar(20)
> );
>
> But it looks like this in the comma-delimited flatfile:
>
> bob     957974401    17    8325824    210.16.68.250
> james     957974401    13    3737270    210.16.68.250
> edwin    957974402    201    3672136    210.16.68.250
> steph    957974404    25    9398998    210.16.68.250
> mitch    957974406    7    7221757    210.16.68.250

I forgot to mention that the error returned is:
ERROR:  copy: line 1, Bad timestamp external representation '957974401'

Thanks,
Brian
--
Brian Baquiran <brianb@edsamail.com>
http://www.baquiran.com/ AIM: bbaquiran
Work: (632)7182222       Home: (632)9227123

brianb-pggeneral@edsamail.com writes:
> I want to copy in some data from a tab-delimited flat file, but one of the
> columns that should translate into a datetime is in Unix timestamp format
> (seconds since epoch).

COPY isn't going to apply any datatype conversions for you.  What you
could do, though, is import into a temp table that has an int4 column
in that position, and then transfer the data to the real table with
something like

insert into RealTable select uname, timestamp(abstime(timeinAsInt4)),
                             duration, etc from TempTable;


Side comment: a lot of people seem to think that COPY is a data
translation utility.  It's not; it was only really intended as a simple
dump/reload method, for which purpose it should be as picky as possible
about the reloaded data.  At some point it'd be nice to have a program
that *is* designed as a data importer and is willing to do data format
conversions for you.  I envision this as a separate client program,
so it wouldn't take any deep dark backend-programming ability to write
it, just some knowledge about typical file formats and conversions.
Anyone want to take on the project?

            regards, tom lane

> insert into RealTable select uname, timestamp(abstime(timeinAsInt4)),
>                              duration, etc from TempTable;

Another side comment: afaik an explicit conversion to abstime is not
required to go from Unix time to timestamp. So

  insert into RealTable select uname, timestamp(timeinAsInt4),
                               duration, etc from TempTable;

should be equivalent.

                     - Thomas

Thomas Lockhart wrote:
>
> > insert into RealTable select uname, timestamp(abstime(timeinAsInt4)),
> >                              duration, etc from TempTable;
>
> Another side comment: afaik an explicit conversion to abstime is not
> required to go from Unix time to timestamp. So
>
>   insert into RealTable select uname, timestamp(timeinAsInt4),
>                                duration, etc from TempTable;
>
> should be equivalent.

Neat.  BTW, the documentation on date/time functions at

    http://www.postgresql.org/docs/postgres/x2876.htm

does not hint at this capability.

Regards,
Ed Loehr

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
> Another side comment: afaik an explicit conversion to abstime is not
> required to go from Unix time to timestamp. So
>   insert into RealTable select uname, timestamp(timeinAsInt4),
>                                duration, etc from TempTable;
> should be equivalent.

Ah, right, that does work.  Just to drift a little further off topic,
the locutions
        timeinAsInt4::timestamp
        cast (timeinAsInt4 as timestamp)
do not work, because in those cases the parser expects to find a
one-step conversion method, and there isn't one --- at least not in
the standard set of Postgres functions.  The function-call syntax works
because there is a function timestamp(abstime) and the parser figures
out it can resolve the ambiguous function name timestamp() as that
function if it first applies int4-to-abstime coercion, which it knows
how to do.

In short: you can get a two-step type conversion from the function call
notation, but only one-step from cast notation.

            regards, tom lane