Subject: Re: unix timestamp
Mime-Version: 1.0 (Apple Message framework v1084)
Content-Type: text/plain; charset=windows-1252
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: 
 <93361C67E4EE844A80935863A4FF4B6C04E86CB9@Exch2010MB-2.univ.dir.wwu.edu>
Date: Thu, 21 Apr 2011 14:59:07 -0500
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <6FC9C366-94F0-4EFC-B882-AA8284020BF3@elevated-dev.com>
References: 
 <93361C67E4EE844A80935863A4FF4B6C04E86CB9@Exch2010MB-2.univ.dir.wwu.edu>
To: Marc Fromm <Marc.Fromm@wwu.edu>

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 =3D =9112/17/2010=92;

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>


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