From: Scott Ribe <scott_ribe@elevated-dev.com>
To: Marc Fromm <Marc.Fromm@wwu.edu>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: unix timestamp
Date: Thu, 21 Apr 2011 14:59:07 -0500
Message-ID: <6FC9C366-94F0-4EFC-B882-AA8284020BF3@elevated-dev.com> (raw)
In-Reply-To: <93361C67E4EE844A80935863A4FF4B6C04E86CB9@Exch2010MB-2.univ.dir.wwu.edu>
References: <93361C67E4EE844A80935863A4FF4B6C04E86CB9@Exch2010MB-2.univ.dir.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 = ‘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@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: scott_ribe@elevated-dev.com, Marc.Fromm@wwu.edu
Subject: Re: unix timestamp
In-Reply-To: <6FC9C366-94F0-4EFC-B882-AA8284020BF3@elevated-dev.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox