/* Date functions in POSTGRESQL */
/* When using a hardcoded date use      -> date 'yyyy-mm-dd', or 'yyyy-mm-dd'::date */
/* When using a hardcoded timestamp use -> timestamp 'yyyy-mm-dd hh:mm:ss', or 'yyyy-mm-dd hh:mm:ss'::timestamp */


/* Obtain the current date/time, date, and time ------------------ */
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;      


/* get the year, month and day ----------------------------------- */
SELECT TO_CHAR(now(),'yyyy'), TO_CHAR(now(),'yy'), TO_CHAR(now(),'mm'), TO_CHAR(now(),'dd');

SELECT EXTRACT(year FROM now()) , EXTRACT(month FROM now()) , EXTRACT(day FROM now());

SELECT TO_CHAR(date '2025-02-25', 'yyyy');                                          /* use TO_CHAR()      */
SELECT EXTRACT(year FROM date '2025-02-25');                                        /* or use EXTRACT()   */
SELECT DATE_PART('year', date '2025-02-25');                                        /* or use DATE_PART() */ 

     
/* Obtain the hour the minute and the second -------------------- */
SELECT TO_CHAR(now(),'hh'), TO_CHAR(now(),'hh24'), TO_CHAR(now(),'mi'), TO_CHAR(now(),'ss'), TO_CHAR(now(),'ms');

SELECT EXTRACT(hour FROM now()) , EXTRACT(minute FROM now()) , EXTRACT(second FROM now());

SELECT DATE_PART('hour', timestamp '2025-02-25 13:15:30');                          /* use timestamp 'yyyy-mm-dd hh:mi:ss' */
SELECT DATE_PART('hour', '2025-02-25 13:15:30'::timestamp);                         /* or 'yyyy-mm-dd hh:mi:ss'::timestamp */


/* Obtain AM/PM and Timezone ----------------------------------- */
SELECT TO_CHAR(now(),'AM'), TO_CHAR(now(),'TZ');


/* Get month name and day name --------------------------------- */
SELECT TO_CHAR(now(),'Month'), TO_CHAR(now(),'Mon'), TO_CHAR(now(),'Day'), TO_CHAR(now(),'Dy');  

SELECT TO_CHAR(date '2025-06-01','month'), TO_CHAR('2025-06-01'::date, 'day');    


/* obtain Julian day, and day of the week ---------------------- */
SELECT TO_CHAR(now(),'ddd'), TO_CHAR(now(),'d');                                /* 1-Sun, 2-Mon, ...*/
                            

/* Obtain the last day of the month ----------------------------- */
SELECT (DATE_TRUNC('month', now()) + INTERVAL '1 month - 1 day')::date AS last_day;


/* Add (or subtract) interval to a date ------------------------- */
SELECT now() + INTERVAL '20 day'  AS new_date;                                  /* you can use year,month,day */ 
SELECT now() + INTERVAL '-100 minute' AS new_time;                              /* you can use hour,minute,second */ 


/* Compute the interval between 2 dates ------------------------- */
SELECT AGE(current_date, '2024-12-31');                                         /* use AGE( ) */

SELECT '2025-01-01'::date - '2024-01-01'::date AS interval_in_days;             /* or simply subtract */       


/* Print the date in a custom format ---------------------------- */
SELECT TO_CHAR(now( ), 'Mon, Dy. mm/dd/yy @ hh:mi:ss AM');                      /* notice spacing & punctuation */


/* Convert a non-standard date to date -------------------------- */
SELECT TO_date('02/27/2025', 'mm/dd/yyyy');