38515

Convert Oracle's timestamp to seconds from epoch

I have a timestamp stored on a table in an Oracle Database, such as:

01/03/12 16:13:33,000000000

I want to convert it to seconds since Unix's Epoch to return in a query. What would be the easiestway to do it?

EDIT: oh, I need timestamp precision and can't rely on Date type here, as it has less precision.

Answer1:

Perhaps

WITH I AS (SELECT (TO_TIMESTAMP_TZ('01/03/12 16:13:33,000000000' || SESSIONTIMEZONE, 'DD/MM/RR HH24:MI:SS,FF9TZH:TZM') - TO_TIMESTAMP_TZ('01/01/1970 00:00:00 GMT', 'DD/MM/YYYY HH24:MI:SS TZR')) AS UNIX_INTERVAL FROM DUAL) SELECT (EXTRACT(DAY FROM UNIX_INTERVAL) * 86400) + (EXTRACT(HOUR FROM UNIX_INTERVAL) * 3600) + (EXTRACT(MINUTE FROM UNIX_INTERVAL) * 60) + (EXTRACT(SECOND FROM UNIX_INTERVAL)) FROM I

will get you started. This assumes a 01-Jan-1970 00:00:00Z epoch date.

Share and enjoy.

Recommend

  • How to parsing NSDate to RFC 822 always use in English?
  • iOS Date formatting
  • Fraction length
  • Validity Method for Reference Classes
  • Jooq casting String to BigDecimal
  • Flex/AS3 very strange simple Number operation issue
  • Oracle - Second level subquery cannot see field from main query
  • Get localized short date pattern as String?
  • cordova is not defined - cordova.js has already been loaded :: Ionic
  • How to detect interior vertices in groups of 2d polygons? (E.g. ZIP Codes to determine a territory)
  • pyodbc doesn't report sql server error
  • Conversion from string “a” to type 'Boolean' is not valid
  • Array with custom indexes in Ionic2
  • Parse a date string in a specific locale (not timezone!)
  • Grails calculated field in SQL
  • Sort List of Strings By Version
  • Set the selected item in dropdownlist in MVC3
  • jQuery .attr() and value
  • Highlight one bar in a series in highcharts?
  • Illegal mix of collations for operation for date/time comparison
  • Getting last autonumber in access
  • Fetching methods from BroadcastReceiver to update UI
  • Java applet as stand-alone Windows application?
  • How to check if every primary key value is being referenced as foreign key in another table
  • MySQL WHERE-condition in procedure ignored
  • Symfony2: How to get request parameter
  • Display Images one by one with next and previous functionality
  • Release, debug version and Authorization Google?
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • GridView Sorting works once only
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • WPF Applying a trigger on binding failure
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Hits per day in Google Big Query
  • How do you join a server to an Active Directory (domain)?
  • How do I configure my settings file to work with unit tests?
  • How does Linux kernel interrupt the application?
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Binding checkboxes to object values in AngularJs