Time calculations and conversions in Oracle and PL/SQL
This article summarizes the methods for performing simple date/time calculations in Oracle and PL/SQL.
The basics
First of all, there are two data types in Oracle: DATE and TIMESTAMP. The primary difference between two of them is that TIMESTAMP supports higher time precision - up to 10-9 seconds. DATE values can be created by parsing a string expression using a particular data format using TO_DATE function and converted back to the formatter string by using TO_CHAR function. For the TIMESTAMP values there is a function called TO_TIMESTAMP (similar to TO_DATE) and the same TO_CHAR function can be used to format timestamp as string.
There are also INTERVAL types used to hold the duration of the time period. We will not be talking about the intervals in this article.
Both DATE and TIMESTAMP values internally use the number of days since January 1, 4712 BC. This is also called "Julian" day number.
Simple date/time arithmetics
Oracle supports a number of arithmetic operations with DATE and TIMESTAMP values. For the adding and subtracting Oracle converts the dates into day numbers. Thus, adding something to a DATE means adding certain number of days to the date. The result is converted back to DATE or TIMESTAMP.
SQL> select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE,'DD- -------------------- 23-OCT-2008 01:20:22 SQL> select to_char(sysdate+2, 'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE+2,'D -------------------- 25-OCT-2008 01:20:22
The number you add or subtract does not have to be integer, thus, you can easily do something like:
SQL> select to_char(sysdate + 1/24 + 3/(60*24), 'DD-MON-YYYY HH24:MI:SS') from dual; TO_CHAR(SYSDATE+1/24 -------------------- 23-OCT-2008 02:23:22
In the example above we add one hour (1/24) and 3 minutes (3/24*60) to the current date.
Obviously, you cannot multiply or divide dates like numbers, this does not make sense.
Subtracting one date from another gives you the number of days between them (which, of course, can be a float number!):
SQL> select sysdate + 1/24 + 3/(60*24) - sysdate from dual; SYSDATE+1/24+3/(60*24)-SYSDATE ------------------------------ .04375
Converting between UNIX timestamp and Oracle DATE/TIMESTAMP
Since you already know that the date/time values are based on the number of days, you can probably imagine how one can calculate the UNIX timestamp from Oracle date. You can use something like this:
SQL> select (sysdate - to_date('1970-01-01 00:00:00',
'YYYY-MM-DD HH24:MI:SS')) * 86400 unix_ts from dual;
UNIX_TS
----------
1224725729
The similar method can be used to convert the UNIX timestamp back to DATE - you just need to add the right number of days to the epoch.
References
blog comments powered by Disqus