Re: 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 about a char column with a formatted date & time, the answer is similar in spirit but different details. You've got to make them 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'd have to compare a range, and calculating that across daylight savings boundaries could be tricky. I'd go for turning the timestamp into a date, probably something like '1970-01-01 00:00:00 UTC'::timestamp + orderdate * interval '1 second'.

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(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice