Thread: unix timestamp

On Apr 21, 2011, at 2:19 PM, Marc Fromm wrote:

> Is there a way to query a unix timestamp date? In the database the orderdate field is a unix timestamp.
> I would like to create the where clause to a query on a specific date like December 17, 2010.
> Select * from orders where orderdate = ‘12/17/2010’;

I'm assuming by unix timestamp, you mean an integer column with number of seconds since 1/1/1970. If you're talking
abouta char column with a formatted date & time, the answer is similar in spirit but different details. You've got to
makethem into the same data type. You can try to make a date from the unix timestamp, or a timestamp from the date. 

The epoch function will get you a unix timestamp directly from a date, but of course that's number of seconds, so then
you'dhave to compare a range, and calculating that across daylight savings boundaries could be tricky. I'd go for
turningthe timestamp into a date, probably something like '1970-01-01 00:00:00 UTC'::timestamp + orderdate * interval
'1second'. 

Of course you don't say whether the timestamps are local times or UTC, and whether you want the date based on local or
UTC,so you'll have to figure that part out. Anyway, for more info, see the date & time functions docs: 

<http://www.postgresql.org/docs/9.0/static/functions-datetime.html>


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On 04/21/2011 12:19 PM, Marc Fromm wrote:

Is there a way to query a unix timestamp date? In the database the orderdate field is a unix timestamp.

I would like to create the where clause to a query on a specific date like December 17, 2010.

Select * from orders where orderdate = ‘12/17/2010’;

 

Thanks

 

Marc

If by "unix timestamp" you mean epoch, just extract the epoch from your date or timestamp:

steve=# select extract(epoch from date '12/17/2010');
 date_part 
------------
 1292572800

Cheers,
Steve