Re: Working with timestamps and Unix Time - Mailing list pgsql-novice

From Andrew McMillan
Subject Re: Working with timestamps and Unix Time
Date
Msg-id 1035961212.2130.241.camel@kant.mcmillan.net.nz
Whole thread Raw
In response to Working with timestamps and Unix Time  ("Kirby Krueger" <kirbyk@idiom.com>)
List pgsql-novice
On Wed, 2002-10-30 at 15:10, Kirby Krueger wrote:
> A quick question:
>
> I have a field of type 'Timestamp'.  I want to extract it in Unix Epoch Time
> format (ie, seconds since Jan 1, 1970.)  What's the syntax to do this in
> Postgres?

SELECT EXTRACT(EPOCH FROM TIMESTAMP '2001-02-16 20:38:40');

    or

SELECT date_part('epoch', TIMESTAMP '2001-02-16 20:38:40');



> And, similarly, when I'm done it'd be awfully convenient if I could stick
> that number right back into the timestamp field.  Looking at the
> documentation, I bet it'll understand it, but does anyone know if there's
> any trick to doing this?

select ('1970-01-01'::timestamp) + 1010101010::interval;


Cheers,
                    Andrew.
--
---------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/         PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201     MOB: +64(21)635-694    OFFICE: +64(4)499-2267
           Survey for free with http://survey.net.nz/
---------------------------------------------------------------------


pgsql-novice by date:

Previous
From: "Kirby Krueger"
Date:
Subject: Working with timestamps and Unix Time
Next
From: "Nikh Nath"
Date:
Subject: Unable to use COPY..FROM with NULL numeric fields