More fun with oracle timestamp math

Posted in: Technical Track

Timestamp Math

Several years ago I wrote an article on Oracle date math.
Amazingly, that article was still available online at the time of this writing.

Working With Oracle Dates

An update to that article is long overdue.
While date math with the DATE data type is fairly well known and straight forward, date math with Oracle TIMESTAMP data is less well known and somewhat more difficult.

Data Types and Functions

Let’s begin by enumerating the data types and functions that will be discussed

Datetime and Interval Data Types

Documentation for Datetime and Interval Data Types

  • Timestamp
  • Timestamp with Time Zone
  • Interval Day to Second
  • Interval Year to Month

Datetime Literals

Documentation for Datetime Literals

  • Date
  • Timestamp
  • Timestamp with Time Zone
  • Timestamp with Local Time Zone

Interval Literals

Documentation for Interval Literals

  • Interval Day to Second
  • Interval Year to Month

Datetime/Interval Arithmetic

Documentation for Datetime/Interval Arithmetic

There is not a link to the heading, just scroll down the page until you find this.

Timestamp Functions

Documentation for Datetime Functions

There quite a few of these available. Most readers will already be familiar with many of these, and so only some of the more interesting functions related to timestamps will be covered.

  • extract
  • to_dsinterval
  • to_yminterval

Timestamp Internals

It is always interesting to have some idea of how different bits of technology work. In Working With Oracle Dates I showed how Date values are stored in the database, as well as how a Date stored in the database is somewhat different than a date variable.

Let’s start by storing some data in a timestamp column and comparing how it differs from systimestamp.

Test table for Timestamp Math Blog:

col c1_dump format a70
col c1 format a35
col funcname format a15
set linesize 200 trimspool on
set pagesize 60
drop table timestamp_test purge;
create table timestamp_test (
c1 timestamp
)
/
insert into timestamp_test values(systimestamp );
insert into timestamp_test values(systimestamp – 366);
commit;
select
‘timestamp’ funcname, c1, dump(c1) c1_dump
from timestamp_test
union all
select
‘systimestamp’ funcname, systimestamp, dump(systimestamp) systimestamp_dump
from dual
/
FUNCNAME        C1                                  C1_DUMP
————— ———————————– ———————————————————————-
timestamp       26-MAR-16 03.09.27.649491 PM -07:00 Typ=180 Len=11: 120,116,3,26,16,10,28,38,182,114,56
timestamp       26-MAR-15 03.09.27.000000 PM -07:00 Typ=180 Len=7: 120,115,3,26,16,10,28
systimestamp    26-MAR-16 03.09.27.687416 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,9,27,0,192,34,249,40,252,0,5,0,0,0,0,0
3 rows selected.

One of the first things you might notice is that the value for Typ is 180 for TIMESTAMP columns, but for SYSTIMESTAMP Typ=188.
The difference is due to TIMESTAMP being an internal data type as stored in the database, while SYSTIMESTAMP is dependent on the compiler used to create the executables.

Another difference is the length; the first TIMESTAMP column has a length of 11, whereas the SYSTIMESTAMP column’s length is 20. And what about that second TIMESTAMP column? Why is the length only 7?

TIMESTAMP with length of 7

An example will show why the second row inserted into TIMESTAMP_TEST has a length of only 7.

  1* select dump(systimestamp) t1, dump(systimestamp-1) t2, dump(sysdate) t3 from dual
15:34:32 ora12c102rac01.jks.com – jkstill@js122a1 SQL- /
T1                                       T2                                       T3
—————————————- —————————————- —————————————-
Typ=188 Len=20: 224,7,3,22,22,34,35,0,16 Typ=13 Len=8: 224,7,3,21,18,34,35,0      Typ=13 Len=8: 224,7,3,22,18,34,35,0
0,181,162,17,252,0,5,0,0,0,0,0
1 row selected.

T2 was implicitly converted to the same data type as SYSDATE because standard date math was performed on it.

The same thing happened when the second row was inserted TIMESTAMP_TEST.

Oracle implicitly converted the data to a DATE data type, and then implicitly converted it again back to a timestamp, only the standard date information is available following the previous implicit conversion.

You may have noticed that in this example the length of the data is 8, while that stored in the table was 7. This is due to the use of SYSDATE, which is an external data type, whereas any data of DATE data type that is stored in the database is using an internal data type which always has a length of 7.

SYSTIMESTAMP Byte Values

Let’s see if we can determine how each byte is used in a SYSTIMESTAMP value

The following SQL will use the current time as a baseline, and start a point 10 seconds previous, showing the timestamp value and the internal representation.

 

col t1 format a35
col t2 format a38
col dump_t1 format a70
col dump_t2 format a70
set linesize 250 trimspool on
/*
 using to_disinterval() allows performing timestamp math without implicit conversions
see https://en.wikipedia.org/wiki/ISO_8601
 for an explanation of the PTnS notation being used in to_dsinterval()
*/
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
— subtract 1 second from the current date
— do it 10 times
select
 –systimestamp t1,
 –dump(systimestamp) dump_t1,
 systimestamp – to_dsinterval(‘PT’ || to_char(level) || ‘S’) t2,
 dump(systimestamp – to_dsinterval(‘PT’ || to_char(level) || ‘S’)) dump_t2
from dual connect by level <= 10
order by level desc
/
T2                                     DUMP_T2
————————————– ———————————————————————-
26-MAR-16 03.34.55.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,55,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.34.56.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,56,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.34.57.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,57,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.34.58.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,58,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.34.59.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,59,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.35.00.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,35,0,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.35.01.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,35,1,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.35.02.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,35,2,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.35.03.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,35,3,0,152,108,205,20,252,0,5,0,0,0,0,0
26-MAR-16 03.35.04.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,35,4,0,152,108,205,20,252,0,5,0,0,0,0,0
10 rows selected.

 

From the output we can see that seconds are numbered 0-59, and that the 7th byte in the internal format is where the second is stored. We can also see that the Month is represented by the 3rd bytes, and the Day by the fourth 4th byte.

One would then logically expect the 5th bite to show us the hour. Glancing at the actual time of 3:00 PM it seems curious then the value we expect to be the hour is 19 rather than 15.

The server where these queries is being run has a Time Zone of EDT. Next I ran the same queries on a server with a TZ of PDT, and though the time in the timestamp appeared as 3 hours earlier, the value stored in the 5th byte is still 19. Oracle is storing the hour in UTC time, then using the TZ from the server to get the actual time.

Playing with Time Zones

We can modify the local session time zone to find out how Oracle is calculating the times.

The first attempt is made on the remote client where scripts for this article are developed. The TZ will be set for Ethiopia and then the time checked at the Linux command line and in Oracle.

 

# date
Sat Mar 26 13:16:12 PDT 2016
# TZ=’Africa/Addis_Ababa’; export TZ
# date
Sat Mar 26 23:16:17 EAT 2016
# sqlplus jkstill/XXX@p1
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
SQL- !date
Sat Mar 26 23:16:40 EAT 2016
SQL-  l
 1 select
 2 systimestamp t1,
 3 dump(systimestamp) dump_t1
 4* from dual
23:16:50 ora12c102rac01.jks.com – jkstill@js122a1
SQL- /
T1                                  DUMP_T1
———————————– ———————————————————————-
26-MAR-16 04.16.56.254769 PM -04:00 Typ=188 Len=20: 224,7,3,26,20,16,56,0,104,119,47,15,252,0,5,0,0,0,0,0

Setting the TZ on the client clearly has no effect on the time returned from Oracle. Now let’s try while logged on to the database server.


$ date
Sat Mar 26 16:20:23 EDT 2016
$ TZ=’Africa/Addis_Ababa’; export TZ
$ date
Sat Mar 26 23:20:38 EAT 2016
$ sqlplus / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
SQL- l
 1 select
 2 systimestamp t1,
 3 dump(systimestamp) dump_t1
 4* from dual
SQL- /
T1                                  DUMP_T1
———————————– ———————————————————————-
26-MAR-16 11.22.48.473298 PM +03:00 Typ=188 Len=20: 224,7,3,26,20,22,48,0,80,244,53,28,3,0,5,0,0,0,0,0

 

This experiment has demonstrated two things for us:

  1. Oracle is storing the hour as UTC time
  2. Setting the TZ on the client does not have any affect on the calculations of the time.

What About the Year?

Given the location of the month, it would be expected to find the year in the byte just previous the month byte. There is not just one byte before the month, but two. You will recall that SYSTIMESTAMP has a different internal representation than does the TIMESTAMP data type. Oracle is using both of these bytes to store the year.

Working with this timestamp from an earlier example, we can use the information in Oracle Support Note 69028.1 to see how this works.

 

T2                                     DUMP_T2
————————————– ———————————————————————-
26-MAR-16 03.34.55.349007000 PM -04:00 Typ=188 Len=20: 224,7,3,26,19,34,55,0,152,108,205,20,252,0,5,0,0,0,0,0

 

For the timestamp of March 16 2016 the first two bytes of the timestamp are used to represent the year.

The Formula for AD dates is Byte 1 + ( Byte 2 * 256 ). Using this formula the year 2016 can be arrived at:

224 + ( 7 * 256) = 2016

For the TIMESTAMP data type, the format is somewhat different for the year; actually it works the same way it does for the DATE data type, in excess 100 notation.

 

SQL- l
1* select c1, dump(c1) dump_c1 from timestamp_test where rownum < 2
SQL- /
C1 DUMP_C1
—————————— —————————————————
26-MAR-16 03.09.27.649491 PM Typ=180 Len=11: 120,116,3,26,16,10,28,38,182,114,56

 

The 2nd byte indicates the current year – 1900.

Decode All Timestamp Components

Now let’s decode all of the data in a TIMESTAMP. First we need some some TIMESTAMP test data

Creating the test data

The following SQL will provide some test data for following experiments.

We may not use all of the columns or rows, but they are available.

 

drop table timestamp_tz_test purge;
create table timestamp_tz_test (
 id integer,
 c1 timestamp,
 c2 timestamp with time zone,
 c3 timestamp with local time zone
)
/
— create 10 rows each on second apart
begin
for i in 1..10
loop
 insert into timestamp_tz_test values(i,systimestamp,systimestamp,systimestamp );
 dbms_lock.sleep(1);
 null;
end loop;
commit;
end;
/

 

We already know that TIMESTAMP data can store fractional seconds to a billionth of a second.

Should you want to prove that to yourself, the following bit of SQL can be used to insert TIMESTAMP data into a table, with each row being 1E-9 seconds later than the previous row. This will be left as an exercise for the reader.

 

create table t2 as
select level id,
 to_timestamp(‘2016-03-29 14:25:42.5′ || lpad(to_char(level),8,’0′),’yyyy-mm-dd hh24.mi.ssxff’) c1
from dual
connect by level <= 1000
/
col dump_t1 format a70
col c1 format a35
col id format 99999999
select id, c1, substr(dump(c1),instr(dump(c1),’,’,-1,4)+1) dump_t1
from t2
order by id
/

 

Oracle uses 4 bytes at the end of a timestamp to store the fractional seconds.

The value of the least byte is as shown.

Each greater byte will be a power of 256.

The following SQL will make this more clear. Don’t spend too much time at first trying to understand the SQL, at it will become more clear after you see the results.

SQL to decode 1 row of TIMESTAMP data.

 

col id format 99
col t1 format a35
col dumpdata format a50
col tz_type format a10
col ts_component format a40
col label format a6
col real_value format a50
set linesize 200 trimspool on
alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24.mi.ssxff’;
alter session set nls_timestamp_tz_format = ‘yyyy-mm-dd hh24.mi.ssxff tzr’;
with rawdata as (
 select c2 t1, dump(c2) dump_t1
 from timestamp_tz_test
 where id = 1
),
datedump as (
 select t1,
 substr(dump_t1,instr(dump_t1,’ ‘,1,2)+1) dumpdata
 from rawdata
),
— regex from https://nuijten.blogspot.com/2009/07/splitting-comma-delimited-string-regexp.html
datebits as (
 select level id, regexp_substr (dumpdata, ‘[^,]+’, 1, rownum) ts_component
 from datedump
 connect by level <= length (regexp_replace (dumpdata, ‘[^,]+’)) + 1
),
labeldata as (
 select ‘TS,DU,CC,YY,MM,DD,HH,MI,SS,P1,P2,P3,P4’ rawlabel from dual
),
labels as (
 select level-2 id, regexp_substr (rawlabel, ‘[^,]+’, 1, rownum) label
 from labeldata
 connect by level <= length (regexp_replace (rawlabel, ‘[^,]+’)) + 1
),
data as (
 select db.id, db.ts_component
 from datebits db
 union
 select 0, dumpdata
 from datedump dd
 union select -1, to_char(t1) from rawdata
)
select d.id, l.label, d.ts_component,
 case l.label
 when ‘DU’ then d.ts_component
 when ‘CC’ then ‘Excess 100 – Real Value: ‘ || to_char(to_number((d.ts_component – 100)*100 ))
 when ‘YY’ then ‘Excess 100 – Real Value: ‘ || to_char(to_number(d.ts_component – 100 ))
 when ‘MM’ then ‘Real Value: ‘ || d.ts_component
 when ‘DD’ then ‘Real Value: ‘ || d.ts_component
 when ‘HH’ then ‘Excess 1 – Real Value: ‘ || to_char(to_number(d.ts_component)-1)
 when ‘MI’ then ‘Excess 1 – Real Value: ‘ || to_char(to_number(d.ts_component)-1)
 when ‘SS’ then ‘Excess 1 – Real Value: ‘ || to_char(to_number(d.ts_component)-1)
 when ‘P1’ then ‘Fractional Second P1 : ‘ || to_char((to_number(d.ts_component) * POWER(256,3) ) / power(10,9))
 when ‘P2’ then ‘Fractional Second P2 : ‘ || to_char((to_number(d.ts_component) * POWER(256,2) ) / power(10,9))
 when ‘P3’ then ‘Fractional Second P3 : ‘ || to_char((to_number(d.ts_component) * 256 ) / power(10,9))
 when ‘P4’ then ‘Fractional Second P4 : ‘ || to_char((to_number(d.ts_component) + 256 ) / power(10,9))
 end real_value
from data d
join labels l on l.id = d.id
order by 1
/

When the values for the Pn fractional second columns are added up, they will be equal to the (rounded) value shown in the timestamp.

 

 ID LABEL  TS_COMPONENT                             REAL_VALUE
— —— —————————————- ————————————————–
 -1 TS     2016-03-31 09.14.29.488265 -07:00
  0 DU     120,116,3,31,17,15,30,29,26,85,40,13,60  120,116,3,31,17,15,30,29,26,85,40,13,60
  1 CC     120                                      Excess 100 – Real Value: 2000
  2 YY     116                                      Excess 100 – Real Value: 16
  3 MM     3                                        Real Value: 3
  4 DD     31                                       Real Value: 31
  5 HH     17                                       Excess 1 – Real Value: 16
  6 MI     15                                       Excess 1 – Real Value: 14
  7 SS     30                                       Excess 1 – Real Value: 29
  8 P1     29                                       Fractional Second P1 : .486539264
  9 P2     26                                       Fractional Second P2 : .001703936
 10 P3     85                                       Fractional Second P3 : .00002176
 11 P4     40                                       Fractional Second P4 : .000000296
13 rows selected.

Timezones are recorded in an additional two bytes in TIMESTAMP WITH TIMEZONE and TIMEAZONE WITH LOCAL TIMEZONE data types.

Decoding those two bytes is left as an exercise for the reader.

Timestamp Arithmetic

Now that we have had some fun exploring and understanding how Oracle stores TIMESTAMP data, it is time to see how calculations can be performed on timestamps.

Note: See this ISO 8601 Article to understand the notation being used in to_dsinterval().

Interval Day to Second

It is a common occurrence to add or subtract time to or from Oracle Dates.

How that is done with the Oracle DATE data type is fairly well known.

  • Add 1 Day
    • DATE + 1
  • Add 1 Hour
    • DATE + (1/24)
  • Add 1 Minute
    • DATE + ( 1 / 1440)
  • Add 1 Second
    • DATE + (1/86400)

Following is a brief refresher on that topic:

 

alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
select sysdate today, sysdate -1 yesterday from dual;
select sysdate now, sysdate – (30/86400) "30_Seconds_Ago" from dual;
select sysdate now, sysdate + ( 1/24 ) + ( 15/1440 ) + ( 42/86400) "1:15:42_Later" from dual;
SQL- @date-calc
Session altered.
TODAY YESTERDAY
——————- ——————-
2016-03-30 13:39:06 2016-03-29 13:39:06
NOW 30_Seconds_Ago
——————- ——————-
2016-03-30 13:39:06 2016-03-30 13:38:36
NOW 1:15:42_Later
——————- ——————-
2016-03-30 13:39:06 2016-03-30 14:54:48

 

While this same method will work with timestamps, the results may not be what you expect. As noted earlier Oracle will perform an implicit conversion to a DATE data type, resulting in truncation of some timestamp data. The next example makes it clear that implicit conversions have converted TIMESTAMP to a DATA type.

alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24.mi.ssxff’;
alter session set nls_date_format = ‘DD-MON-YY’;
select systimestamp today, systimestamp -1 yesterday from dual;
select systimestamp now, systimestamp – (30/86400) "30_Seconds_Ago" from dual;
select systimestamp now, systimestamp + ( 1/24 ) + ( 15/1440 ) + ( 42/86400) "1:15:42_Later" from dual;
SQL- @timestamp-calc-incorrect
TODAY                                                                       YESTERDAY
————————————————————————— ———
2016-03-31 11.35.29.591223 -04:00                                           30-MAR-16
NOW                                                                         30_Second
————————————————————————— ———
2016-03-31 11.35.29.592304 -04:00                                           31-MAR-16
NOW                                                                         1:15:42_L
————————————————————————— ———
2016-03-31 11.35.29.592996 -04:00                                           31-MAR-16

 

Oracle has supplied functions to properly perform calculations on timestamps. The previous example will work properly when ds_tointerval is used as seen in the next example.

 

col c30 head ’30_Seconds_Ago’ format a38
col clater head ‘1:15:42_Later’ format a38
col now format a35
col today format a35
col yesterday format a38
alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24.mi.ssxff’;
alter session set nls_timestamp_tz_format = ‘yyyy-mm-dd hh24.mi.ssxff tzr’;
— alternate methods to subtract 1 day
select systimestamp today, systimestamp – to_dsinterval(‘P1D’) yesterday from dual;
select systimestamp today, systimestamp – to_dsinterval(‘1 00:00:00’) yesterday from dual;
— alternate methods to subtract 30 seconds
select systimestamp now, systimestamp – to_dsinterval(‘PT30S’) c30 from dual;
select systimestamp now, systimestamp – to_dsinterval(‘0 00:00:30’) c30 from dual;
— alternate methods to add 1 hour, 15 minutes and 42 seconds
select systimestamp now, systimestamp + to_dsinterval(‘PT1H15M42S’) clater from dual;
select systimestamp now, systimestamp + to_dsinterval(‘0 01:15:42’) clater from dual;
TODAY                               YESTERDAY
———————————– ————————————–
2016-03-30 18.10.41.613813 -04:00 2016-03-29 18.10.41.613813000 -04:00
TODAY                               YESTERDAY
———————————– ————————————–
2016-03-30 18.10.41.614480 -04:00 2016-03-29 18.10.41.614480000 -04:00
NOW                                 30_Seconds_Ago
———————————– ————————————–
2016-03-30 18.10.41.615267 -04:00 2016-03-30 18.10.11.615267000 -04:00
NOW                                 30_Seconds_Ago
———————————– ————————————–
2016-03-30 18.10.41.615820 -04:00 2016-03-30 18.10.11.615820000 -04:00
NOW                                 1:15:42_Later
———————————– ————————————–
2016-03-30 18.10.41.616538 -04:00 2016-03-30 19.26.23.616538000 -04:00
NOW                                 1:15:42_Later
———————————– ————————————–
2016-03-30 18.10.41.617161 -04:00 2016-03-30 19.26.23.617161000 -04:00

 

Extract Values from Timestamps

The values for years, months, days, hours and seconds can all be extracted from a timestamp via the extract function. The following code demonstrates a few uses of this, along with examples of retrieving intervals from two dates.

The values in parentheses for the day() and year() intervals specify the numeric precision to be returned.

 

def nls_tf=’yyyy-mm-dd hh24.mi.ssxff’
alter session set nls_timestamp_format = ‘&nls_tf’;
col d1_day format 999999
col full_interval format a30
col year_month_interval format a10
with dates as (
   select
      to_timestamp_tz(‘2014-06-19 14:24:29.373872’, ‘&nls_tf’) d1
      , to_timestamp_tz(‘2016-03-31 09:42:16.8734921’, ‘&nls_tf’) d2
   from dual
)
select
   extract(day from d1) d1_day
   , ( d2 – d1) day(4) to second full_interval
   , ( d2 – d1) year(3) to month year_month_interval
   , extract( day from d2 – d1) days_diff
   , extract( hour from d2 – d1) hours_diff
   , extract( minute from d2 – d1) minutes_diff
   , extract( second from d2 – d1) seconds_diff
from dates
/
 D1_DAY FULL_INTERVAL                  YEAR_MONTH  DAYS_DIFF HOURS_DIFF MINUTES_DIFF SECONDS_DIFF
——- —————————— ———- ———- ———- ———— ————
     19 +0650 19:17:47.499620          +001-09           650         19           17   47.4996201

Building on that, the following example demonstrates how the interval value the represents the difference between dates d1 and d2 can be added back to d1 and yield a date with the same value as d1.

 

def nls_tf=’yyyy-mm-dd hh24.mi.ssxff’
alter session set nls_timestamp_format = ‘&nls_tf’;
col d1 format a30
col d2 format a30
col full_interval format a30
col calc_date format a30
with dates as (
   select
      to_timestamp(‘2014-06-19 14:24:29.373872’, ‘&nls_tf’) d1
      , to_timestamp(‘2016-03-31 09:42:16.873492’, ‘&nls_tf’) d2
   from dual
)
select
   d1,d2
   , ( d2 – d1) day(4) to second  full_interval
   , d1 + ( d2 – d1) day(4) to second calc_date
from dates
/
D1                             D2                             FULL_INTERVAL                  CALC_DATE
—————————— —————————— —————————— ——————————
2014-06-19 14.24.29.373872000  2016-03-31 09.42.16.873492000  +0650 19:17:47.499620          2016-03-31 09.42.16.873492000

 

PL/SQL Interval Data Types

 

The ISO 8601 Article previously mentioned will be useful for understanding how time durations may be specified with interval functions.

The following combination of SQL and PL/SQL is used to convert the difference between two timestamps into seconds. The code is incomplete in the sense that the assumption is made that the largest component of the INTERVAL is hours. In the use case for this code that is true, however there could also be days, months and years for larger value of the INTERVAL.

The following code is sampled from the script ash-waits-use.sql and uses PL/SQL to demonstrate the use of the INTERVAL DAY TO SECOND data type in PL/SQL.

 

var v_wall_seconds number
col wall_seconds new_value wall_seconds noprint
declare
	ash_interval interval day to second;
begin
	select max(sample_time) – min(sample_time) into ash_interval from v$active_session_history;
	select
		max(sample_time) – min(sample_time) into ash_interval
	from v$active_session_history
	where sample_time
	between
		decode(‘&&snap_begin_time’,
			‘BEGIN’,
			to_timestamp(‘1900-01-01 00:01′,’yyyy-mm-dd hh24:mi’),
			to_timestamp(‘&&snap_begin_time’,’yyyy-mm-dd hh24:mi’)
		)
		AND
		decode(‘&&snap_end_time’,
			‘END’,
			to_timestamp(‘4000-12-31 23:59′,’yyyy-mm-dd hh24:mi’),
			to_timestamp(‘&&snap_end_time’,’yyyy-mm-dd hh24:mi’)
		);
	:v_wall_seconds :=
		(extract(hour from ash_interval) * 3600 )
		+ (extract(second from ash_interval) * 60 )
		+ extract(second from ash_interval) ;
end;
/
select round(:v_wall_seconds,0) wall_seconds from dual;

 

Similarly the to_yminterval function is used to to perform timestamp calculations with years and months.


col clater head ‘LATER’ format a38
col now format a35
col today format a35
col lastyear format a38
col nextyear format a38
alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24.mi.ssxff’;
alter session set nls_timestamp_tz_format = ‘yyyy-mm-dd hh24.mi.ssxff tzr’;
— alternate methods to add 1 year
select systimestamp today, systimestamp + to_yminterval(‘P1Y’) nextyear from dual;
select systimestamp today, systimestamp + to_yminterval(’01-00′) nextyear from dual;
— alternate methods to subtract 2 months
select systimestamp now, systimestamp – to_yminterval(‘P2M’) lastyear from dual;
select systimestamp now, systimestamp – to_yminterval(’00-02′) lastyear from dual;
— alternate methods to add 2 year, 4 months, 6 days ,1 hour, 15 minutes and 42 seconds
select systimestamp now, systimestamp + to_yminterval(‘P2Y4M’)  + to_dsinterval(‘P2DT1H15M42S’) clater from dual;
select systimestamp now, systimestamp + to_yminterval(’02-04′)  + to_dsinterval(‘2 01:15:42’) clater from dual;
TODAY                               YESTERDAY
———————————– ————————————–
2016-03-31 09.06.22.060051 -07:00   2016-03-30 09.06.22.060051000 -07:00
TODAY                               YESTERDAY
———————————– ————————————–
2016-03-31 09.06.22.061786 -07:00   2016-03-30 09.06.22.061786000 -07:00
NOW                                 30_Seconds_Ago
———————————– ————————————–
2016-03-31 09.06.22.063641 -07:00   2016-03-31 09.05.52.063641000 -07:00
NOW                                 30_Seconds_Ago
———————————– ————————————–
2016-03-31 09.06.22.064974 -07:00   2016-03-31 09.05.52.064974000 -07:00
NOW                                 1:15:42_Later
———————————– ————————————–
2016-03-31 09.06.22.066259 -07:00   2016-03-31 10.22.04.066259000 -07:00
NOW                                 1:15:42_Later
———————————– ————————————–
2016-03-31 09.06.22.067600 -07:00   2016-03-31 10.22.04.067600000 -07:00

While date math with the DATE data type is somewhat arcane, it is not too complex once you understand how it works.

When Oracle introduced the TIMESTAMP data type, that all changed. Timestamps are much more robust than dates, and also more complex

Timestamps bring a whole new dimension to working with dates and times; this brief introduction to working with timestamp data will help demystify the process of doing math with timestamps.

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Oracle experience: started with Oracle 7.0.13 Programming Experience: Perl, PL/SQL, Shell, SQL Also some other odds and ends that are no longer useful Systems: Networking, Storage, OS to varying degrees. Have fond memories of DG/UX

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *