Обсуждение: Working with timestamps and Unix Time

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?

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?

Thanks for any help!

-- Kirby

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