How to convert timestamp to epoch?

[Antonio]

Hi,

The date in the database is stored in this format YYYY-MM-DD-
HH24:MI:SS
During the select query,
I would like to convert this date format in "Unix epoch".
How can I manage the SQL query to make it?

Thanks,

Antonio

[Rob Wolfe]

Google is everyone's friend ...

http://blogs.sun.com/mock/entry/converting_oracle_dates_to_unix

Rob

[Javier Montani]

Check these examples:

http://geekswithblogs.net/SanjayU/archive/2006/04/14/75198.aspx

2009/5/19 Rob Wolfe <wolf...@gmail.com>

[ddf]

Comments embedded.

On May 19, 11:27 am, Antonio <antonio.pie...@gmail.com> wrote:
> Hi,
>
> The date in the database is stored in this format YYYY-MM-DD-
> HH24:MI:SS

No, it's stored in a proprietary format and what you see is one of
many display format options.

> During the select query,
> I would like to convert this date format in "Unix epoch".

You want UNIX format, as the epoch is a single date from which all
other UNIX dates are referenced.

If you know the definition for the UNIX epoch you should be able to do
this quite easily. If not here's some help:

UNIX epoch: 01/01/1970 00:00:00

UNIX time is seconds/milliseconds since the epoch

86400 seconds in a day

86400000 milliseconds in a day

Simply subtract the epoch from your Oracle date and multiply by the
factor of your choice (depending upon whether you want seconds since
the epoch or milliseconds since the epoch) and you're done.

> How can I manage the SQL query to make it?
>

As an example:

SQL> select (sysdate - to_date('01/01/1970','MM/DD/RRRR'))*86400
seconds_since_UNIX_epoch
2 from dual;

SECONDS_SINCE_UNIX_EPOCH
------------------------
1242744131

If you want milliseconds:

SQL> select (sysdate - to_date('01/01/1970','MM/DD/RRRR'))*86400000
milliseconds_since_UNIX_epoch
2 from dual;

MILLISECONDS_SINCE_UNIX_EPOCH
-----------------------------
1242744159000


> Thanks,
>
> Antonio


David Fitzjarrell