WARNING

This information is provided with no warranty of any kind-- USE AT YOUR OWN RISK.  It is provided here because it is a simple question from a lot of new oracle developers and is often answered by well-meaning DBAs that think in terms of modifying the data to a new format in the same or a new column.  Our solution works in any language because it is in the SQL passed to the Oracle instance and is not related to the underlying language used by the developer.

Retrieving Oracle "timestamp" as "unixseconds":

This is a common question for developers interfacing with a database but is often answered by oracle gurus that are thinking about changing the data in the database. Meaning well, but obfuscating the issue the developer is left confused. Here is the most simple SQL syntax that will accomplish this in any developer language (because this is the submitted SQL).

Try this:

select trunc((myunixcolumndata - TO_DATE(\'01-01-1970\',\'MM-DD-YYYY\')) * 86400) as unixtimesecs;

Works great.  Now your oracle timestamp value will be returned as a UNIX seconds since 1/1/1970 midnight.  Oracle stores as UTC so you will need to make a timezone adjustment for returned data.

Good luck!
David

If you have any other oracle, informix, mysql, postgresql needs let us know.