From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Brooksby <rb(at)ravenbrook(dot)com> |
Cc: | "Alan T(dot) Miller" <amiller(at)hollywood101(dot)com>, pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Convert a UNIX timestamp in a PostgreSQL INSERT statement |
Date: | 2004-04-05 02:33:26 |
Message-ID: | 29246.1081132406@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Richard Brooksby <rb(at)ravenbrook(dot)com> writes:
> If you have a Unix-style timestamp (seconds since 1970-01-01 00:00) you
> can convert it to a PostgreSQL timestamp like this:
> select timestamp '1970-01-01' + interval '953559481 seconds';
If it's really truly a Unix timestamp, that is seconds since 1970-01-01
00:00 GMT, you need to say "timestamptz" or "timestamp with time zone"
and specify that you want GMT zone. The above references the interval
to 1970-01-01 midnight your local time, and will therefore be wrong by
the amount of your offset from GMT.
The recommended way is really
select timestamptz 'epoch' + 953559481 * interval '1 second';
where 'epoch' is just a slightly more mnemonic way of writing
'1970-01-01 00:00 GMT'. Using the number-times-interval operator as
I've done here is optional, but you'll find it's a good habit to get
into, because this way is much more convenient as soon as you start
doing anything even a little bit complicated. The other way tends to
lead you into wanting to do ugly, error-prone things with concatenating
strings together and then converting them to interval...
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Nabil Sayegh | 2004-04-05 03:29:27 | snowflaking |
Previous Message | joseph speigle | 2004-04-04 15:31:57 | Re: problem with psql |