date function in postgresql

-----------------------
select date_trunc('month', current_date) - interval '1 month' - interval '1 day'
--Which means: truncate the current date to the first day of the month, then go back 1 month and one day.

SELECT
    justify_days(INTERVAL '30 days'),
    justify_hours(INTERVAL '24 hours');
SELECT
    justify_interval(interval '1 year -1 hour');

--find which weekof month
TO_CHAR( registration_date , 'W' )::integer as weekofmonth
extract to_char as INT/String
--find hour in date
date_part('hour', order_time) as hour_ordered
date_part('dow',registration_date)  as dayofweek
-- extract date_part as INT
---
extract(dow from order_time) as dayofweek
-------------------------------------------------------------------------------------------------
--if we want to use date in one column, our row in column value cannot contain string, we can use the option nullif(column name, 'string') to replace string into NULL

PARSING DATE
DATE change text value to date column, but the string has to be in digit only
date('monday, june 10th 2014') = error because DATE only expect STRING IN DIGIT(0-9) NOT A-Z
--so we can use

TO_date(date_String, format_string) = date value
--The first argument is the string that you want to convert to a date.
-- The second one is the input format. The TO_DATE() function returns a date value.
to_Date('monday, june 10th 2014', 'Day, mm , yyyy') = DATE value
SELECT TO_DATE('20170103','YYYYMMDD') >  2017-01-03
SELECT TO_DATE('2017 Feb 20','YYYY Mon DD'); >  2017-02-20



--------------------------------------------------------------------------------
TO_CHAR(expression, format)
(1)The expression can be a timestamp, an interval, an integer, a double precision, or a numeric value that is converted to a string according to a specific format. > so its a DIGIT
(2) pattern like MM YY DD

SELECT TO_CHAR(NOW() :: DATE, 'dd/mm/yyyy'); >  23/06/2016
SELECT TO_CHAR(NOW() :: DATE, 'Mon dd, yyyy'); >  Jun 23, 2016
SELECT TO_CHAR( TIMESTAMP '2017-08-18 22:30:59', 'HH24:MI:SS'); > 22:30:59

	EXTRACT (YEAR FROM birth_date) AS YEAR,
	EXTRACT (MONTH FROM birth_date) AS MONTH,
	EXTRACT (DAY FROM birth_date) AS DAY

----------------------------------------------------------------------------    
now() - hire_date as diff > get interval between two date data
 
-------------------------------------------------------------------------
DATE_PART(field,source) --almost the same as extract, we getting the one value in our date
SELECT date_part('year',TIMESTAMP '2017-01-01'); > 2017
SELECT date_part('dow',TIMESTAMP '2017-03-18 10:20:30') dow,
       date_part('doy',TIMESTAMP '2017-03-18 10:20:30') doy;
 dow | doy
-----+-----
   6 |  77
-------------------------------------
date_trunc('datepart', field) --trunc a specific date into a precision time
SELECT DATE_TRUNC('hour', TIMESTAMP '2017-03-17 02:09:30'); >  2017-03-17 02:00:00
SELECT date_trunc('minute', TIMESTAMP '2017-03-17 02:09:30'); >  2017-03-17 02:09:00