Re: Working with timestamps and Unix Time

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/
---------------------------------------------------------------------