More fun with oracle timestamp math

by Jared Still on Apr 4, 2016 12:00:00 AM

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:

[sourcecode language="sql"] 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. [/sourcecode]

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.

[sourcecode language="sql"] 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. [/sourcecode]

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.

[sourcecode language="sql"] 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. [/sourcecode]

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.

[sourcecode language="sql"] # 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 [/sourcecode]

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.

[sourcecode language="sql"] $ 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 [/sourcecode]

This experiment has demonstrated two things for us:

Oracle is storing the hour as UTC time
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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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; / [/sourcecode]

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.

[sourcecode language="sql"] 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 / [/sourcecode]

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.

[sourcecode language="sql"] 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 / [/sourcecode]

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

[sourcecode language="sql" padlinenumbers="false"] 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. [/sourcecode]

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:

[sourcecode language="sql" padlinenumbers="true"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="sql"] 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 [/sourcecode]

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.

[sourcecode language="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; [/sourcecode]

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

[sourcecode language="sql" padlinenumbers="true"] 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 ----------------------------------- ----------------

Topics: MySQL Technical Track Open Source Innodb Cluster Mysql 5.7

No Comments Yet

Let us know what you think

Share this