MySQL Interview Questions and Answers
Ques 6. How To Decrement Dates by 1 in MySQL?
If you have a date, and you want to decrement it by 1 day, you can use the DATE_SUB(date, INTERVAL 1 DAY) function. You can also use the date interval subtraction operation as "date - INTERVAL 1 DAY". The tutorial exercise below gives you some good examples:
SELECT DATE_SUB(DATE('1997-03-01'), INTERVAL 1 DAY)
FROM DUAL;
1997-02-28
SELECT DATE('1997-01-31') - INTERVAL 1 DAY FROM DUAL;
1997-02-28
Ques 7. How To Calculate the Difference between Two Dates?
If you have two dates, and you want to know how many days between them, you can use the DATEDIFF(date1, date2) function as shown below:
SELECT DATEDIFF(DATE('1997-02-28'), DATE('1997-03-01'))
FROM DUAL;
-1
Ques 8. How To Calculate the Difference between Two Time Values?
If you have two time values, and you want to know the time difference between them, you can use the TIMEDIFF(time1, time2) function as shown below:
SELECT TIMEDIFF(TIME('19:26:50'), TIME('09:26:50'))
FROM DUAL;
10:00:00
SELECT TIMEDIFF('1997-03-01 19:26:50.000123',
'1997-02-28 09:26:50.000000') FROM DUAL;
34:00:00.000123
Ques 9. How To Present a Past Time in Hours, Minutes and Seconds?
If you want show an article was posted "n hours n minutes and n seconds ago", you can use the TIMEDIFF(NOW(), pastTime) function as shown in the following tutorial exercise:
SELECT TIMEDIFF(NOW(), '2006-07-01 04:09:49') FROM DUAL;
06:42:58
SELECT TIME_FORMAT(TIMEDIFF(NOW(), '2006-06-30 04:09:49'),
'%H hours, %i minutes and %s seconds ago.') FROM DUAL;
30 hours, 45 minutes and 22 seconds ago.
Ques 10. How To Extract a Unit Value from a Date and Time?
If you want to extract a specific date or time unit value out of a date or a time, you can use the EXTRACT(unit FROM expression) function. The tutorial exercise below gives you some good examples:
ELECT EXTRACT(DAY FROM NOW()) FROM DUAL;
28
ELECT EXTRACT(HOUR FROM NOW()) FROM DUAL;
23
ELECT EXTRACT(SECOND FROM NOW()) FROM DUAL;
36
Most helpful rated by users: