How to convert UNIX epochs to datetime values in Oracle Database (and back again!)

UNIX epochs are the number of seconds after 1st Jan 1970, midnight UTC (00:00)*. Oracle Database has no inbuilt format to convert epochs to standard datetime values and vice-versa. In this post we’ll look at how to:

• Turn an epoch to a datetime value
• Go from datetime values to epochs
• Handle the year 2038 problem
• Work with non-UTC time zones
• Manage leap seconds
• Convert epochs to date with JavaScript stored procedures

Read to the end for a cheat sheet summary.

Thanks to Chris Hoina for reviewing this article.

Change epochs to datetimes in Oracle Database

If you have an epoch value and want to find out which “human” datetime value this corresponds to, then:

• Convert the epoch to a number of seconds
• Add this to midnight 1st Jan 1970

Which looks like either:

Note the different logic for date and timestamp values. This is because they have different arithmetic rules.

The unit for date arithmetic is the day. So you need to divide the epoch by the number of seconds in a day (24 hours * 60 minutes * 60 seconds = 86,400) to convert it into a number of days.

To increase a timestamp, you need to add an interval. This is a length of time specified in units of years to months or days down to seconds. The function numtodsinterval converts a number to a day to second interval in the units provided.

While you can add a number of days to timestamps instead of an interval, this implicitly converts them to dates. This loses any fractional seconds or time zone information. It also changes the logic for later calculations. This can lead to surprising results. Generally you want to avoid this.

Change datetimes to epochs in Oracle Database

To go the other way and turn a human datetime into an epoch, subtract 1 Jan 1970 from the input and convert this to the number of seconds:

Again, dates and timestamps have different logic. The difference between

• Two dates is the number of days between them
• Two timestamps is the duration as a day to second interval
• A date and a timestamp is also the duration as a day to second interval

So when dealing dates you just need to multiply the result up by 86,400.

It’s tricker with timestamps.

To turn a (day to second) interval to seconds, you need to extract each part (days, hours, minutes, and seconds). Then multiply each up by the number of seconds in that unit. The cross apply subquery is a neat way to create a local "column variable".

Numeric overflow errors (the year 2038 problem)

3:14:08 AM on 19th Jan 2038 is 2^31 seconds after midnight 1st Jan 1970. Which is too large to fit into a PL/SQL pls_integer (or any signed integer)!

This could cause problems if you’re assigning epochs to pls_integer or related types. Try it and you'll get ORA-01426: numeric overflow errors:

To get around this use unconstrained types, such as a plain integer.

Calculating epochs for non-UTC time zones

UNIX epochs are always in the UTC time zone (00:00). If you’re working with dates in another time zone you’ll need to convert them. You can do this with a combination of from_tz and the at time zone expression.

• from_tz takes a timestamp value and time zone and returns a timestamp with time zone
• at time zone converts a value from one time zone to another

So to convert an epoch to a non-UTC time zone

1. Use the epoch-to-datetime formula above to get the value
   1. (optional if working with dates) cast this to a timestamp value
2. Return this from_tz ( …, 'UTC' )
3. Return the result of this at the target time zone
4. (optional if working with dates) cast back to a date value. Note this loses the time zone!

For example, this converts an epoch to the corresponding time in Kolkata, India:

If you want to turn a non-UTC datetime value into an epoch, reverse the steps above:

1. Use from_tz to add the time zone data
   1. If it’s a date, cast it to a timestamp first.
2. Return this at time zone 'UTC'
3. Use the formulas above to turn this into an epoch

Here we’re going from the time in Los Angeles, USA to the corresponding epoch:

If you’re working with timestamps that include the time zone, you can skip the from_tz step. The database already has this data! Go straight to UTC:

Leap seconds

* Why the asterisk at the start? Well the UNIX epoch isn’t really the number of seconds since 1 Jan 1970. Just as years can have extra days, they can also have an extra second added. This has happened 27 times so far!

Luckily(?) epoch time ignores leap seconds. Oracle Database datetime formulas also make no provision for leap seconds. So there’s no need to change calculations to account for these.

Tread carefully if you need logic supporting leap seconds – particularly when they are occurring!

Create epoch conversion functions with JavaScript stored procedures

These conversions - particularly for timestamp values - can be fiddly. JavaScript has methods for converting between epochs and calendar dates. It would be nice if you could use the JavaScript functions in the database.

The good news is from Oracle Database 23c you can! This adds the capability to define JavaScript stored procedures. So you can write JavaScript routines which you access in the same way as PL/SQL functions.

Oracle Database 23c Free - Developer Release is the first release of the next-generation Oracle Database, allowing developers a head-start on building applications with innovative 23c features that simplify development of modern data-driven apps. The entire  feature set of Oracle Database 23c is planned to be generally available within the next 12 months.

Here are examples in action:

More datetime issues

Dates are complicated! Handling dates, timestamps, and intervals incorrectly is the source of many errors. To help you understand how to use these values here's the recording of my Ask TOM Office Hours session on this topic:

Want to learn SQL itself? Join our free beginner's SQL course.

Epoch to datetimes cheat sheet

Here's a cheat sheet summarizing the logic in this post

UPDATE 25 May 2023: Added section on using JavaScript to do the conversions in 23c