Oracle data type TIMESTAMP

There are three variants of the timestamp data type:

timestamp
timestamp with time zone
timestamp with local time zone

A timestamp differs from a date that it is able to store fractions of a second. A date can only store integral values for seconds.

A timestamp with local time zone does not store the additional information that a timestamp with time zone stores - but it allows to use the TZH:TZM or TZR TZD format elements.

Substractions

Most resulting data types of a substraction where a timestamp is involved is an interval day to second.

A notable exception is the substraction of a number from a timestamp which results in a date.

drop table timestamp_diff purge;

create table timestamp_diff as
select
   timestamp '2022-08-28 17:36:12.78'        -            1234.56                        as ts_nm,          -- date
   timestamp '2022-08-28 17:36:12.78'        - date      '1998-09-13'                    as ts_dt,          -- interval day(9) to second(9)
   timestamp '2022-08-28 17:36:12.78'        - timestamp '1998-09-13 06:51:38.41'        as ts_ts,          -- interval day(9) to second(9)
   timestamp '2022-08-28 17:36:12.78 +05:00' - timestamp '1998-09-13 06:51:38.41 +09:00' as ts_tz__ts_tz,   -- interval day(9) to second(9)
   date      '2022-08-28'                    - timestamp '1998-09-13 06:51:38.41'        as dt_ts           -- interval day(9) to second(9)
from
   dual
;

desc timestamp_diff;

Github repository Oracle-Patterns, path: /SQL/datatypes/timestamp/subtract.sql

Calculate difference between two timestamps in seconds

drop table ts;

create table ts (ts timestamp);

insert into ts values (timestamp '2021-02-05 14:01:02.78');
insert into ts values (timestamp '2021-02-06 15:02:03.79');

select
   a.ts ts_end,
   b.ts ts_begin,
   extract(day from   (a.ts - b.ts)   * 86400   ) seconds_between,
   extract(day from    a.ts - b.ts  ) * 86400     wrong_calculation,
   extract(day from ( (a.ts - b.ts)   * 86400 ) ) use_more_parantheses
from
   ts a join ts b on a.ts > b.ts;

Compare with getting the duration of an interval in seconds.

Timestamp literal

Without fractional seconds: TIMESTAMP '2021-08-17 21:14:52'.

With fractional seconds: TIMESTAMP '2021-08-17 21:14:52.241…'.

Compare with the date literal.

Timezone related

column sessiontimezone        format a6
column col_timestamp          format a24
column col_timestamp_tz       format a31
column col_timestamp_tz_loc   format a24

create table tq84_date_data_types (
  col_date                      date,
  col_timestamp                 timestamp,
  col_timestamp_tz              timestamp with       time zone,
  col_timestamp_tz_loc          timestamp with local time zone
);

insert into tq84_date_data_types values (sysdate, sysdate, sysdate, sysdate);


select sessiontimezone, dt.* from tq84_date_data_types dt;
--
-- SESSIO COL_DATE            COL_TIMESTAMP            COL_TIMESTAMP_TZ                COL_TIMESTAMP_TZ_LOC
-- ------ ------------------- ------------------------ ------------------------------- ------------------------
-- +01:00 06.12.2016 16:36:48 06.12.16 16:36:48.000000 06.12.16 16:36:48.000000 +01:00 06.12.16 16:36:48.000000

alter session set time_zone = '-3:00';

insert into tq84_date_data_types values (sysdate, sysdate, sysdate, sysdate);

select sessiontimezone, dt.* from tq84_date_data_types dt;
--
-- SESSIO COL_DATE            COL_TIMESTAMP            COL_TIMESTAMP_TZ                COL_TIMESTAMP_TZ_LOC
-- ------ ------------------- ------------------------ ------------------------------- ------------------------
-- -03:00 06.12.2016 16:36:48 06.12.16 16:36:48.000000 06.12.16 16:36:48.000000 +01:00 06.12.16 12:36:48.000000
-- -03:00 06.12.2016 16:36:48 06.12.16 16:36:48.000000 06.12.16 16:36:48.000000 -03:00 06.12.16 16:36:48.000000

drop table tq84_date_data_types purge;

Github repository Oracle-Patterns, path: /SQL/datatypes/timestamp/timezone-01.sql

Convert a timestamp from one timezone to another:

select
   timestamp '2021-08-21 22:23:24 Europe/Zurich' AT TIME ZONE 'America/Anchorage'
from
  dual;

Oracle data type TIMESTAMP

Substractions

Calculate difference between two timestamps in seconds

Formatting a timestamp

Timestamp literal

Timezone related

See also