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:
Read to the end for a cheat sheet summary.
Thanks to Chris Hoina for reviewing this article.
If you have an epoch value and want to find out which “human” datetime value this corresponds to, then:
Which looks like either:
Note the different logic for
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.
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:
timestamps have different logic. The difference between
dates is the number of days between them
timestamps is the duration as a
day to second interval
timestampis 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
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".
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
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.
timestampvalue and time zone and returns a
timestamp with time zone
at time zoneconverts a value from one time zone to another
So to convert an epoch to a non-UTC time zone
dates) cast this to a
from_tz ( …, 'UTC' )
dates) cast back to a
datevalue. 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:
from_tzto add the time zone data
date, cast it to a
at time zone 'UTC'
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:
* 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!
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:
Dates are complicated! Handling
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.
Here's a cheat sheet summarizing the logic in this post
Chris Saxon is an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL!