/* Date functions in MySQL */

/* Obtain the current date/time.  All are equivalent ------------- */
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;      


/* get the year, month and day ----------------------------------- */
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());      


/* Obtain the hour the minute and the second -------------------- */
SELECT HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());


/* get names of month and of day --------------------------------- */
SELECT MONTHNAME(NOW()),  DAYNAME('2021-03-06');  


/* obtain julian day, day of the month, and day of the week ------ */
SELECT DAYOFYEAR(NOW()), DAYOFMONTH(NOW()), DAYOFWEEK(NOW());       /* 1-Sun, 2-Mon, ...*/
                            

/* Obtain the last day of the month ----------------------------- */
SELECT LAST_DAY('2024-02-01'),  LAST_DAY('2025-02-01'); 


/* Add 20 days to the current day ------------------------------- */
SELECT TIMESTAMPADD(DAY, 20, NOW( ) );

SELECT DATE_ADD(NOW( ), INTERVAL 20 DAY);   


/* Subtract 150 minutes, a.k.a. 2 & 1/2 hours ------------------- */
SELECT TIMESTAMPADD(MINUTE, -150, NOW( ) );

SELECT DATE_SUB(NOW( ), INTERVAL 150  MINUTE);  
                        

/* Compute the number of days in between 2 dates ----------------- */
SELECT TIMESTAMPDIFF(DAY, NOW(), '2024-12-31');     /* 2024-12-31' minus now( ) */  

SELECT DATEDIFF('2024-12-31', now() );              /* 2024-12-31' minus now( ) */      

SELECT (unix_timestamp(now()) - unix_timestamp('2024-12-31')) / (24*60*60);


/* Print the date in a custom format ---------------------------- */
SELECT DATE_FORMAT(NOW( ), '%W,  %M %e   %Y');      /* notice spacing & punctuation */

SELECT DATE_FORMAT(NOW( ), '%b,  %c/%e/%Y');   


/* Convert a non-standard date into a date ---------------------- */
SELECT STR_TO_DATE('10/3/2024', '%m/%d/%Y');   

SELECT STR_TO_DATE('March 7 2024 - 5:20:15', '%M %e %Y - %h:%i:%s');    


/* Obtain the last Friday of a month ---------------------------- */
SELECT  
   CASE DAYOFWEEK(LAST_DAY(NOW())) 
      WHEN 1 THEN TIMESTAMPADD(day, -2, LAST_DAY(now()))     /* if Sunday    */
      WHEN 1 THEN TIMESTAMPADD(day, -3, LAST_DAY(now()))     /* if Monday    */
      WHEN 1 THEN TIMESTAMPADD(day, -4, LAST_DAY(now()))     /* if Tuesday   */
      WHEN 1 THEN TIMESTAMPADD(day, -5, LAST_DAY(now()))     /* if Wednesday */
      WHEN 1 THEN TIMESTAMPADD(day, -6, LAST_DAY(now()))     /* if Thursday  */
      WHEN 1 THEN TIMESTAMPADD(day,  0, LAST_DAY(now()))     /* if Friday    */
      WHEN 1 THEN TIMESTAMPADD(day, -1, LAST_DAY(now()))     /* if Saturday  */
   END as "Last Friday of Month" ;


/* Adding 45 "business/non-weekend" days to a date ------------------ */
SELECT
  TIMESTAMPADD(DAY, 45 + FLOOR((DAYOFWEEK(NOW( )) + (45*7/5)) / 7) * 2, NOW())  
  as "Adding 45 Business Days" ;  

/* Exlanation:

DAY                         calculation using unit of DAY                  
45                          45 days                                        
+                           plus an additional (how many weekend days?):   
dayofweek(NOW( ))           which day 1-7 of the week is this day? Wed = 4 
(45 * 7/5)                  45 business days = 63.0 regular days           
adding the above 2 lines    4 + 63.0 = 108.0                               
dividing by 7               how many weeks is this = 15.4285 weeks         
flooring the above          15.4285 weeks have 15 weekends                 
multiplying by 2            30 - I need to add 30 additional weekend days    
NOW( )                      starting with now( ), or any other date        */ 

/*--------------------------------------------------------------------------------*/