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





-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Reply via email to