Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Introduction to SQL Date and Time Handling

By: FYIcenter.com

Part:   1  2  3  4   5 

(Continued from previous part...)

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.

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

What Are Date and Time Functions?

MySQL offers a number of functions for date and time values:

  • ADDDATE(date, INTERVAL expr unit) - Adding days to a date. Same as DATE_ADD().
  • ADDTIME(time1, time2) - Adding two time values together.
  • 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_ADD(date, INTERVAL expr unit) - Adding days to a date.
  • 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.
  • EXTRACT(unit FROM date) - Returning a unit value of a date and time.
  • HOUR(time) - Returning the hour value of a time
  • LAST_DAY(date) - Returning a date representing the last day of the month based on the given date.
  • LOCALTIME() - Returning the current date and time. Same as NOW().
  • MAKETIME(hour,minute,second) - Returning a date based on the given time unit values.
  • MICROSECOND(time) - Returning the microsecond unit value of the given time.
  • MINUTE(time) - Returning the minute unit value of the given time.
  • MONTH(date) - Returning the month unit value of the given date.
  • MONTHNAME(date) - Returning the month name of the given date.
  • NOW() - Returning the current date and time.
  • SECOND(time) - Returning the second unit value of the given time.
  • SEC_TO_TIME(seconds) - Converting a second value to a time
  • STR_TO_DATE(str,format) - Converting a character string to a date.
  • SUBDATE(date,INTERVAL expr unit) - Subtracting days from a date.
  • SUBTIME(expr1,expr2) - Subtracting a time from another time.
  • SYSDATE() - Returning current date and time.
  • TIME(expr) - Returning a time from the given date and time.
  • TIMEDIFF(expr1,expr2) - Returning the difference between two times.
  • TIME_TO_SEC(time) - Converting a time to a second value.
  • WEEKDAY(date) - Returning the weekday unit value of a given date.
  • YEAR(date) - Returning the year unit value of a given date.

What Is TIMESTAMP?

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:

    CREATE TABLE links (lastDate TIMESTAMP);

CREATE TABLE links (lastDate TIMESTAMP 
   DEFAULT CURRENT_TIMESTAMP
   ON UPDATE CURRENT_TIMESTAMP);

    (Continued on next part...)

    Part:   1  2  3  4   5 


Selected Developer Jobs:

More...