Demonstration of calculation and casting of DATE, TIME and TIMESTAMP on Postgresql.

postgres_1_date_time_timestamp_cast_and_calculations.sql

CREATE TABLE ordered_products (                                                                     
 id SERIAL PRIMARY KEY,
 name VARCHAR(50),
 department VARCHAR(50),
 price INTEGER,
 weight NUMERIC);

# Let's add some columns with TIMESTAMP data.
ALTER TABLE ordered_products ADD COLUMN created TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE ordered_products ADD COLUMN updated TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE ordered_products ADD COLUMN ordered TIMESTAMP WITH TIME ZONE;
ALTER TABLE ordered_products ADD COLUMN delivered TIMESTAMP WITH TIME ZONE;

postgres_2_insert_data.sql

INSERT INTO
  ordered_products (name, department, price, weight)
VALUES
  ('Television', 'Electronics', 500, 13),
  ('Bicycle', 'Sports', 250, 18),
  ('King size bed', 'Home', 385, 25),
  ('Microwave Oven', 'Home', 345, 10),
  ('Wardrobe', 'Home', 220, 42);

# Let's set timestamps for created and updated columns
UPDATE ordered_products SET created = '2021-08-30', updated = '2021-08-30' WHERE department = 'Electronics';
UPDATE ordered_products SET created = '2021-09-24', updated = '2021-09-24' WHERE department = 'Sports';
UPDATE ordered_products SET created = '2021-10-15', updated = '2021-10-15' WHERE department = 'Home';

# Let's also set timestamps for customer orders (when the product was bought) and delivered timestamps.
UPDATE ordered_products SET ordered = '2021-11-19', delivered = '2021-11-21' WHERE name = 'King size bed';
UPDATE ordered_products SET ordered = '2021-11-19', delivered = '2021-11-24' WHERE name = 'Microwave Oven';
UPDATE ordered_products SET ordered = '2021-11-13', delivered = '2021-11-20' WHERE name = 'Television';

# Let's defined some delivery deadlines
UPDATE ordered_products SET deadline = '2021-11-18' WHERE name = 'Television';
UPDATE ordered_products SET deadline = '2021-11-24' WHERE name = 'King size bed';
UPDATE ordered_products SET deadline = '2021-11-24' WHERE name = 'Microwave Oven';


SELECT * FROM ordered_products ORDER BY Id;
 id |      name      | department  | price | weight |        created         |        updated         |        ordered         |       delivered        |        deadline        
----+----------------+-------------+-------+--------+------------------------+------------------------+------------------------+------------------------+------------------------
  1 | Television     | Electronics |   500 |     13 | 2021-08-30 00:00:00+00 | 2021-08-30 00:00:00+00 | 2021-11-13 00:00:00+00 | 2021-11-20 00:00:00+00 | 2021-11-18 00:00:00+00
  2 | Bicycle        | Sports      |   250 |     18 | 2021-09-24 00:00:00+00 | 2021-09-24 00:00:00+00 |                        |                        | 
  3 | King size bed  | Home        |   385 |     25 | 2021-10-15 00:00:00+00 | 2021-10-15 00:00:00+00 | 2021-11-19 00:00:00+00 | 2021-11-21 00:00:00+00 | 2021-11-24 00:00:00+00
  4 | Microwave Oven | Home        |   345 |     10 | 2021-10-15 00:00:00+00 | 2021-10-15 00:00:00+00 | 2021-11-19 00:00:00+00 | 2021-11-24 00:00:00+00 | 2021-11-24 00:00:00+00
  5 | Wardrobe       | Home        |   220 |     42 | 2021-10-15 00:00:00+00 | 2021-10-15 00:00:00+00 |                        |                        | 
(5 rows)

postgres_3_cast_date.sql

# All these queries will return the same result..
SELECT ('2021-09-24'::DATE);
SELECT ('2021-Sep-24'::DATE);
SELECT ('2021 Sep 24'::DATE);
SELECT ('Sep 24 2021'::DATE);
SELECT ('Sep/24/2021'::DATE);
SELECT ('2021/Sep/24'::DATE);
SELECT ('2021-Sep-24'::DATE);
SELECT ('2021----Sep----24'::DATE);
SELECT ('2021_Sep_24'::DATE);
SELECT ('24 September 2021'::DATE);

    date    
------------
 2021-09-24
(1 row)

# If other words, acronyms like 'th' or 'of' comes with the string, the DATE cast will not work 
SELECT ('24 of September of 2021'::DATE);
LINE 1: SELECT ('24 of September of 2021'::DATE);
                ^
SELECT ('24 September of 2021'::DATE);
LINE 1: SELECT ('24 September of 2021'::DATE);

postgres=# SELECT ('24th September 2021'::DATE);
LINE 1: SELECT ('24th September 2021'::DATE);

postgres_4_cast_time.sql

postgres=# SELECT ('16:45:59'::TIME);
   time   
----------
 16:45:59
(1 row)

postgres=# SELECT ('16:45:59'::TIME WITH TIME ZONE);
   timetz    
-------------
 16:45:59+00
(1 row)

postgres=# SELECT ('16:45:59 EST'::TIME WITH TIME ZONE);
   timetz    
-------------
 16:45:59-05
(1 row)

postgres=# SELECT ('04:45:59 PM EST'::TIME WITH TIME ZONE);
   timetz    
-------------
 16:45:59-05
(1 row)

postgres_5_cast_timestamp.sql

postgres=# SELECT ('2021-12-24'::TIMESTAMP);
      timestamp      
---------------------
 2021-12-24 00:00:00
(1 row)

postgres=# SELECT ('20211224T141024'::TIMESTAMP);
      timestamp      
---------------------
 2021-12-24 14:10:24
(1 row)

postgres=# SELECT ('20211224T141024-0500'::TIMESTAMP WITH TIME ZONE);
      timestamptz       
------------------------
 2021-12-24 19:10:24+00
(1 row)

postgres_6_queries_and_calculations.sql

SELECT name, created::DATE FROM ordered_products;
      name      |  created   
----------------+------------
 Television     | 2021-09-24
 Bicycle        | 2021-09-24
 King size bed  | 2021-11-30
 Microwave Oven | 2021-11-30
 Wardrobe       | 2021-11-30
(5 rows)


SELECT name, (delivered - ordered) AS time_invested FROM ordered_products;
      name      | time_invested 
----------------+---------------
 Bicycle        | 
 Wardrobe       | 
 Television     | 7 days
 King size bed  | 2 days
 Microwave Oven | 5 days
(5 rows)

SELECT
  name, ordered, delivered, (delivered - ordered) AS time_invested, deadline,
  CASE
    WHEN deadline >= delivered THEN 'Success'
    WHEN deadline < delivered THEN 'Fail'
  END AS dealine_achievement
FROM ordered_products;

      name      |        ordered         |       delivered        | time_invested |        deadline        | dealine_achievement 
----------------+------------------------+------------------------+---------------+------------------------+---------------------
 Bicycle        |                        |                        |               |                        | 
 Wardrobe       |                        |                        |               |                        | 
 Television     | 2021-11-13 00:00:00+00 | 2021-11-20 00:00:00+00 | 7 days        | 2021-11-18 00:00:00+00 | Fail
 King size bed  | 2021-11-19 00:00:00+00 | 2021-11-21 00:00:00+00 | 2 days        | 2021-11-24 00:00:00+00 | Success
 Microwave Oven | 2021-11-19 00:00:00+00 | 2021-11-24 00:00:00+00 | 5 days        | 2021-11-24 00:00:00+00 | Success
(5 rows)