8.Date transformations.sql

use ecom;
-- DATE TRANSFORMATIONS (Very Important)

select * from dim_date;
    
-- Functions used to fetch current date

select
	date,
    now() as 'current_timestamp', -- timestamp according to our timezone
    utc_date() as 'current_date', -- use single quotes as it is a reserved keyword
    utc_time() as  'current_utc_time',
    utc_timestamp() as 'utc_timestamp'
from
	dim_date;

-- Extract components from date

select
	date,
    year(date),
    month(date),
    day(date),
    week(date),
    weekday(date),
    dayname(date),
    date(utc_timestamp()), -- Extract date from timestamp
    adddate(date,2), -- Add 2 days from date
    subdate(date,2) -- Subtract 2 days from date
from
	dim_date;

-- Application 1 : Find orders placed before 30 days

select
	datediff(date(utc_timestamp),date) as total_days
from 
	dim_date;
    
-- Casting string to date

select 
	cast('25-01-01' as datetime) -- cast is used to convert any type to another (type casting)
from
	dim_date;
    
-- Convert date formats

select
	date,
    date_format(date,'%W %M %e %Y') as converted_date -- There are lot of such patterns (
from
	dim_date;