SQL : Date/ Time and TimeStamps

This guide is for PostgreSQL : https://www.postgresql.org/

Getting Started with PostgreSQL :
https://dev117uday.gitbook.io/databases/sql/getting-started

Loading Sample Data Guide :
https://dev117uday.gitbook.io/databases/sql/getting-started/load-data

Set Date Time Style

-- show system date style
SHOW datestyle;

-- set new datestyle
SET datestyle = 'ISO, DMY';
SET datestyle = 'ISO, MDY';

Make

SELECT MAKE_DATE (2020,01,01);
 make_date  
------------
 2020-01-01

SELECT MAKE_DATE (2020,01,01);
 make_date  
------------
 2020-01-01

SELECT MAKE_TIME(2,3,14.65);
  make_time  
-------------
 02:03:14.65

SELECT MAKE_TIMESTAMP (2020,02,02,10,20,45.44);

    make_timestamp     
------------------------
 2020-02-02 10:20:45.44

Make_interval

SELECT MAKE_INTERVAL (2020,01,02,10,20,33);

          make_interval           
-----------------------------------
 2020 years 1 mon 24 days 20:33:00

SELECT MAKE_INTERVAL (days => 10);

make_interval 
---------------
 10 days

SELECT MAKE_INTERVAL (months => 7, days => 10, mins=>35);

     make_interval      
-------------------------
 7 mons 10 days 00:35:00

SELECT MAKE_INTERVAL (weeks => 10);

make_interval 
---------------
 70 days

Make_timestamptz

SELECT make_timestamptz(2020,02,02,10,30,45.55,'Asia/Calcutta');

     make_timestamptz      
---------------------------
 2020-02-02 05:00:45.55+00

SELECT pg_typeof(make_timestamptz(2020,02,02,10,30,45.55));

        pg_typeof         
--------------------------
 timestamp with time zone

Date Value Extractor

• function datetime

• PostgreSQL extract

Extract

select extract ('day' FROM current_timestamp), extract ('month' FROM current_timestamp), extract ('year' FROM current_timestamp);

 date_part | date_part | date_part 
-----------+-----------+-----------
        14 |         8 |      2021

select extract('epoch' FROM current_timestamp);

     date_part     
-------------------
 1628923887.158532


select extract('century' FROM current_timestamp);

 date_part 
-----------
        21

Maths Operations on Date Time

select '2020-02-02'::date + 04;

  ?column?  
------------
 2020-02-06

select '23:59:59' + INTERVAL '1 SECOND';

 ?column? 
----------
 24:00:00

select '23:59:59' + INTERVAL '2 SECOND';

 ?column? 
----------
 24:00:01

SELECT CURRENT_TIMESTAMP + '01:01:01';

           ?column?            
-------------------------------
 2021-08-14 07:53:05.444791+00

SELECT DATE '20200101' + TIME '10:25:10';

      ?column?       
---------------------
 2020-01-01 10:25:10

SELECT '10:10:10' + TIME '10:25:10';

 ?column? 
----------
 20:35:20

SELECT DATE '20200101' - INTERVAL '1 HOUR';

      ?column?       
---------------------
 2019-12-31 23:00:00

SELECT INTERVAL '30 MINUTES' + '2 HOUR';

 ?column? 
----------
 02:30:00

Overlap

select
    ( DATE '2020-01-01' , DATE '2020-12-31' )
    OVERLAPS
    ( DATE '2020-12-30', DATE '2020-12-01' );

 overlaps 
----------
 t

Current

select
    current_date,
    current_time,
    current_time(2),
    current_timestamp;

 current_date |    current_time    |  current_time  |       current_timestamp       
 2021-08-14   | 06:53:52.187847+00 | 06:53:52.19+00 | 2021-08-14 06:53:52.187847+00


select localtime,
    localtimestamp,
    localtimestamp(2);

    localtime    |       localtimestamp       |     localtimestamp     
-----------------+----------------------------+------------------------
 06:54:07.540777 | 2021-08-14 06:54:07.540777 | 2021-08-14 06:54:07.54


select
   now(),
   transaction_timestamp(),
   clock_timestamp();

now | transaction_timestamp | clock_timestamp        

2021-08-14 06:54:31.371838+00 | 2021-08-14 06:54:31.371838+00 | 2021-08-14 06:54:31.371924+00


select statement_timestamp(),
   timeofday();

statement_timestamp      |              timeofday              
-------------------------------+-------------------------------------
 2021-08-14 06:55:07.202782+00 | Sat Aug 14 06:55:07.202849 2021 UTC

Age

select age('2020-01-01', '2019-10-01');

  age   
--------
 3 mons

select age(timestamp '2020-01-01');

          age          
-----------------------
 1 year 7 mons 13 days

select age(current_date, '2020-01-01');

          age          
-----------------------
 1 year 7 mons 13 days

Epochs

select age ( timestamp '2020-12-20', timestamp '2020-10-20' );

  age   
--------
 2 mons


SELECT 
    EXTRACT (EPOCH FROM TIMESTAMPTZ '2020-10-20')
    - EXTRACT (EPOCH FROM TIMESTAMPTZ '2020-08-20') 
        AS "DIFFERENCE IN SECONDS";

 DIFFERENCE IN SECONDS 
-----------------------
               5270400

Timezone

SELECT * FROM pg_timezone_names;

SELECT * FROM pg_timezone_abbrevs;

SHOW TIME ZONE;

SET TIME ZONE 'Asia/Calcutta';

date_part and date_trunc

SELECT date_part ('day', date '2021-11-07');

 date_part 
-----------
         7


SELECT date_trunc('hour', 
    timestamptz '2021-07-16 23:38:40.775719 +05:30');

       date_trunc       
------------------------
 2021-07-16 18:00:00+00