mysql date functions | date operations in mysql

mysql date functions | date operations in mysql

case :- 

• when extract dates between  two dates.
• When we can get dates between two dates in mysql than we can fire one single query.
• with two parameters first is startDate and second is endDate. 
• It display dates between this two dates under the selected_date title.

Input :-

startDate =2017-01-05

endDate   =2017-01-10

Output :- 

--------------------------
| selected_date |
--------------------------
| 2017-01-05    |
| 2017-01-06    |
| 2017-01-07    |
| 2017-01-08    |
| 2017-01-09    |
| 2017-01-10    |
--------------------------

Query :-

 

select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between 'startDate' and 'endDate'

-------------------------------------------------------------------------------------------------------------------

case :- 

• when we create dummy table based on table insert dates than we can setup select insert and dates.
• here, you can cange date you want.

Query :-

INSERT INTO destination_table_name (col1,col2)
SELECT col1, col2
FROM source_table_name
WHERE insert_date < '2017-02-01 00:00:00';

-------------------------------------------------------------------------------------------------------------------

case :-

• when we get time difference in two times.
• you can just pass two difference times in this syntax.
• you get time difference.

input format(hh:MM:ss) :-

1  09:12:30
2  08:11:25

output format(hh:MM:ss) :-

-------------------
|Difference|
-------------------
|01:00:55  |
-------------------  

Query :-

select SUBTIME('09:12:30','08:11:35');

-------------------------------------------------------------------------------------------------------------------

case :-  

• when we get date difference in two date.
• you have just two difference date in this syntax.

Functionality :-

--------------------------------------------------------------------
| input differences   |      output                  |
--------------------------------------------------------------------
| days                      |     X Days X Hours    |
| hours                    |    X Hours X Min       |
| Min                       |    X Min X sec           |
--------------------------------------------------------------------

Here, X is value.

Must be :- 

Format of date :- yyyy-MM-dd hh:mm:ss

Example           :- 2016-10-04 16:32:40

Input :-

--------------------------------------------------------------------------

|    start_time               |         end_time           |  
--------------------------------------------------------------------------

| 2016-10-04 16:32:40 | 2016-10-04 16:32:48 |
| 2016-10-04 16:32:41 | 2016-10-04 16:33:01 |
| 2016-10-04 16:32:40 | 2016-10-04 16:33:01 |
| 2016-10-04 16:32:51 | 2016-10-04 16:33:03 |
| 2016-10-04 16:32:49 | 2016-10-04 16:33:02 |
| 2016-10-04 16:32:54 | 2016-10-04 16:33:09 |
--------------------------------------------------------------------------

Output :-

-------------------------------------------------------------------------------------------------
|    start_time              |         end_time          |ExecutionTime|   
-------------------------------------------------------------------------------------------------
|2016-10-04 16:32:40 | 2016-10-04 16:32:48  | 0 Min 8 Sec   |
|2016-10-04 16:32:41 | 2016-10-04 16:33:01  | 0 Min 20 Sec |
|2016-10-04 16:32:40 | 2016-10-04 16:33:01  | 0 Min 21 Sec |
|2016-10-04 16:32:51 | 2016-10-04 16:33:03  | 0 Min 12 Sec |
|2016-10-04 16:32:49 | 2016-10-04 16:33:02  | 0 Min 13 Sec |
|2016-10-04 16:32:54 | 2016-10-04 16:33:09  | 0 Min 15 Sec |
------------------------------------------------------------------------------------------------

Query :-

SELECT j.start_time,j.end_time,
 CASE
                        WHEN j.start_time IS NOT NULL
                             AND j.end_time IS NOT NULL
                             THEN CASE
                                  WHEN FLOOR(HOUR(TIMEDIFF(j.end_time,j.start_time)) / 24) != 0 THEN CONCAT(FLOOR(HOUR(TIMEDIFF(j.end_time,j.start_time)) / 24), ' Days ', MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24), ' Hours ')
                             ELSE CASE
                                  WHEN MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24) != 0 THEN CONCAT(MOD(HOUR(TIMEDIFF(j.end_time, j.start_time)), 24), ' Hours ', MINUTE(TIMEDIFF(j.end_time, j.start_time)), ' Min ')
                             ELSE CONCAT(MINUTE(TIMEDIFF(j.end_time, j.start_time)), ' Min ', SECOND(TIMEDIFF(j.end_time, j.start_time)), ' Sec')
                             END
                             END
                        ELSE 'N/A'
                    END AS 'ExecutionTime'
                    FROM job_detail j;