pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedunix timestamp
3+ messages / 3 participants
[nested] [flat]
* unix timestamp
@ 2011-04-21 19:19 Marc Fromm <Marc.Fromm@wwu.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Marc Fromm @ 2011-04-21 19:19 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unix timestamp
@ 2011-04-21 19:59 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Scott Ribe @ 2011-04-21 19:59 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unix timestamp
@ 2011-04-22 16:54 Steve Crawford <scrawford@pinpointresearch.com>
parent: Marc Fromm <Marc.Fromm@wwu.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Crawford @ 2011-04-22 16:54 UTC (permalink / raw)
To: Marc Fromm <Marc.Fromm@wwu.edu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-04-22 16:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-04-21 19:19 unix timestamp Marc Fromm <Marc.Fromm@wwu.edu>
2011-04-21 19:59 ` Scott Ribe <scott_ribe@elevated-dev.com>
2011-04-22 16:54 ` Steve Crawford <scrawford@pinpointresearch.com>
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