Skip site navigation (1) Skip section navigation (2)

Re: Working with timestamps and Unix Time

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: Kirby Krueger <kirbyk(at)idiom(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Working with timestamps and Unix Time
Date: 2002-10-30 07:00:12
Message-ID: 1035961212.2130.241.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: 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/ 
---------------------------------------------------------------------


In response to

pgsql-novice by date

Next:From: Scott TaylorDate: 2002-10-30 13:21:05
Subject: PgAccess Visual Query
Previous:From: Kirby KruegerDate: 2002-10-30 02:10:50
Subject: Working with timestamps and Unix Time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group