RE: date format in epoch

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Tue, 23 May 2006 16:58:12 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF27080F7B73@AABO-EXCHANGE02.bos.il.pqe>

Well, you could try reversing the query from Jan-Carel, below...shouldn't be too difficult....

Hint #1:  There are 60*60*24  = 86400 seconds in a day.
Hint #2:  The epoch is usually known to be 01-JAN-1970 00:00:00.
Hint #3:  You can add a (decimal, non-integer) number of days to a date

datatype and get a date that many days (or fractions of a day) into the future (or the past if you make it a negative number).

-Mark

--

Mark J. Bobak
Senior Oracle Architect
ProQuest Information & Learning

For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anthony Ettinger Sent: Tuesday, May 23, 2006 4:08 PM
To: cjpengel.dbalert_at_xs4all.nl
Cc: Oracle-L_at_freelists.org
Subject: Re: date format in epoch

I have a timestamp in seconds since the epoch, how do I get it back into a human-readable date?

ie - to_date('$secs_since_epoch', 'yyyymmdd') ?

On 5/16/06, Carel-Jan Engel <cjpengel.dbalert_at_xs4all.nl> wrote:

To get sysdate converted in seconds since 01-JAN-1970 try this:

select (SYSDATE - TO_DATE('01011970000000' , 'ddmmyyyyhh24miss')) * 86400 AS epoch_date

	FROM   dual
	/

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

On Tue, 2006-05-16 at 12:04 -0700, Anthony Ettinger wrote:

I have dates in the database as "seconds since epoch"..

I need to select items where start_time >= epoch(sysdate
- 7);

How do I get the sysdate converting to epoch easily within the sql code?

--

Anthony Ettinger
Signature: http://chovy.dyndns.org/hcard.html

--

http://www.freelists.org/webpage/oracle-l Received on Tue May 23 2006 - 15:58:12 CDT