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: (view raw or whole 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 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;

Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St,  Wellington
WEB:         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 

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-2015 The PostgreSQL Global Development Group