- Search for "Withoutbook Practice Exam Test" in Mobile/Tablet Play Store
 Take a Tour Practice InterviewNew Search by Name or Email Play Games Questions Forum

Exams Attended

Mock Exams

Make Homepage

Ques 86. How To Calculate the Difference between Two Time Values?

Ans. 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 87. How To Present a Past Time in Hours, Minutes and Seconds?
Ans. 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 88. How To Extract a Unit Value from a Date and Time?
Ans. 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
Ques 89. What Are Date and Time Functions in MySQL?
Ans. MySQL offers a number of functions for date and time values:

► CURDATE() - Returning the current date. Same as CURRENT_DATE().
► CURTIME() - Returning the current time. Same as CURRENT_TIME().
► DATE(expression) - Returning the date from the expression.
► DATEDIFF(date1, date2) - Returning dates difference in days.
► DATE_SUB(date, INTERVAL expr unit) - Subtracting days from a date.
► DATE_FORMAT(date, format) - Returning a character string representing a date.
► DAY(date) - Returning an integer representing the day of the month. Same as DAYOFMONTH()
► DAYNAME(date) - Returning the name of week day.
► DAYOFMONTH(date) - Returning an integer representing the day of the month.
► DAYOFWEEK(date) - Returning an integer representing the day of the week.
► DAYOFYEAR(date) - Returning an integer representing the day of the year.
Ques 90. What Is TIMESTAMP in MySQL?
Ans. A TIMESTAMP data type allows you to record a date and time like DATETIME data type. But it has some interesting features when used on a table column:

► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an INSERT statement.
► The first TIMESTAMP column in a table will be assigned with the current date and time, if it is not listed in an UPDATE statement.
► If a TIMESTAMP column is assigned with NULL, the current date and time will be persisted to this column.
► You can turn off the default current date and time on INSERT by defining the column with "DEFAULT 0".
► The tutorial exercise below provides you 2 equivalent ways to create a TIMESTAMP column: