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

MySQL FAQs - Introduction to SQL Date and Time Handling

By: FYIcenter.com

Part:   1   2  3  4  5 

A collection of 15 FAQs on MySQL date and time handling. Clear answers are provided with tutorial exercises on date and time data types; formatting and converting date and times; incrementing dates and times; calculating date differences; understanding TIMESTAMP columns. Topics included in this collection are:

  1. What Are Date and Time Data Types?
  2. How To Write Date and Time Literals?
  3. How To Enter Microseconds in SQL Statements?
  4. How To Convert Dates to Character Strings?
  5. How To Convert Character Strings to Dates?
  6. What Are Date and Time Intervals?
  7. How To Increment Dates by 1?
  8. How To Decrement Dates by 1?
  9. How To Calculate the Difference between Two Dates?
  10. How To Calculate the Difference between Two Time Values?
  11. How To Present a Past Time in Hours, Minutes and Seconds?
  12. How To Extract a Unit Value from a Date and Time?
  13. What Are Date and Time Functions?
  14. What Is TIMESTAMP?
  15. How Many Ways to Get the Current Time?

Please note that all answers and tutorials are based on MySQL 5.0. Tutorial exercises should be executed with "mysql" or other MySQL client programs.

What Are Date and Time Data Types?

MySQL supports the following date and time data types:

  • DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".
  • DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
  • TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".
  • TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
  • TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".
  • YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".

How To Write Date and Time Literals?

MySQL offers a number of formats for you to use to enter date and time literals:

  • ANSI standard format: "YYYY-MM-DD HH:MM:SS".
  • Non-standard limiters. Like: "YYYY/MM/DD HH^MM^SS" or "YYYY.MM.DD HH-MM-SS".
  • No limiters. Like: "YYYYMMDD" for a date or "HHMMSS" for a time.
  • Decimal numbers. Like: 8 digits dddddddd for a date or 6 digits dddddd for a time.

The tutorial exercise below gives you some good examples:

SELECT DATE('1997-01-31') FROM DUAL;
   1997-01-31

SELECT DATE('1997-01-31 09:26:50') FROM DUAL;
   1997-01-31

SELECT TIME('1997-01-31 09:26:50') FROM DUAL;
   09:26:50

SELECT DATE('1997/01/31 09^26^50') FROM DUAL;
   1997-01-31

SELECT TIME('1997/01/31 09^26^50') FROM DUAL;
   09:26:50

SELECT DATE('19970131') FROM DUAL;
   1997-01-31

SELECT TIME('092650') FROM DUAL;
   09:26:50

SELECT DATE(19970131) FROM DUAL; -- Crazy format
   1997-01-31

SELECT TIME(092650) FROM DUAL; -- Crazy format
   09:26:50

How To Enter Microseconds in SQL Statements?

If you want to enter microseconds in a SQL statements, you can enter them right after the time string as a 6-digit number delimited with '.'. '0' will be padded to right if not enough digits. Here are some good examples:

SELECT TIME('1997/01/31 09^26^50.123') FROM DUAL;
   09:26:50.123000
   
SELECT TIME('1997/01/31 09^26^50.000123') FROM DUAL;
   09:26:50.000123

(Continued on next part...)

Part:   1   2  3  4  5 


Selected Developer Jobs:

More...