Date and Time Literals
Date and Time in MySQL – Part 1
Forward: In this part of the series, I talk about the literals for date and time in MySQL.
By: Chrysanthus Date Published: 3 Sep 2012
Introduction
Prerequisite
This tutorial set is part of my volume titled, The Must Know in Database Design and MySQL. The link to the web page for this volume is given below (bottom of page). If you click it you will see all the series and their links (syllabus) of the volume. The items on the syllabus page have been listed in the order in which you should study. From there you would know what you should have studied before reaching here.
Use of Date and Time
It is not uncommon to find a table in a database that has a column for dates that certain actions (or sales) took place. You would also find tables that have a column for the times that certain actions took place.
Use of the String
The date or time is best typed in a string. In the SQL statement to insert a row into the table, the date or time is commonly typed in a string, such as, '2012-05-21' or '22:45:56' respectively. The date, '2012-05-21' can also be written without the quotes as in 20120521. This number is formed by removing the quotes and the separations. I do not recommend that you use this number type of date in your SQL statements. This date number is not an ordinary number in the sense that it is not a counting number. So, in your SQL statements, I advise you to use dates in a string form as often as possible.
I will spend the rest of this part of the series to talk about date and time types. Remember, when creating a table in MySQL you have to specify the type of data that the column will take. So far as date and time are concerned, I give you the different types in this part of the series.
The date data value in a MySQL SQL statement should be written as 'YYYY-MM-DD' or 'YY-MM-DD', where YYYY is a 4-digit year and YY is a 2-digit year. I advise you to always use the 4-digit year. Examples are: '1998-02-15' and '12-11-03'. MM is month in 2 digits from January to December, with January being 01 and December being 12. DD is a day of the month from 01 to a maximum of 31.
The hyphens can be replaced with / , so '1998/02/15' is a valid date to write in a SQL statement.
Also note in the format that the date begins with the year, then month and then day of month. This order can be changed, but I will not go into that.
The Time Data Type
The value of a time is written in the format, 'HH:MM:SS'. Here, HH is hours in 2 digits from 00 to 23 where 00 is midnight and 23 is 11pm. MM is minutes in 2 digits from 00 to 59, spanning 60 minutes in one hour. SS is seconds in 2 digits from 00 to 59 spanning 60 seconds in one minute. An example of time value is, 13:30:45. Do not forget to use the colon as separation mark. You normally would type a date or time value in a string in a SQL statement, when inserting a row into a table. Note: you can also have a number time type, similar to the date number type; however, I do not encourage you to use that.
Datetime Data Type
The datetime data type is just a combination of the date and time. It is the date and the time of the day when something happened. The format is: 'YYYY-MM-DD HH:MM:SS'. Note the single space between the date and the time values in the string. An example is: '2028-01-19 03:14:17'. So you can have a column of a table with data type, DATETIME. As you can see, you can have a column with data type, DATE or data type, TIME, or data type, DATETIME. A column can also have a data type, TIMESTAMP or YEAR (see below).
The timestamp data type is the same as the datetime data type, but in the case of the timestamp, it is the computer (MySQL server) that writes the time. It is not you who writes the time in the SQL statement, as you would do for the datetime. If you are inserting a row in a table where one column has the type, TIMESTAMP, you do not have to provide for (do not write column name and value for) the timestamp in the SQL statement. The timestamp value is the date and time at which the row is inserted. MySQL server does that (insertion) for you.
The Year Data Type
The Year type format is 'YYYY' or 'YY'. In MySQL 'YYYY' is from 1901 to 2155, while 'YY' is in the range, 70 to 00 to 69 where 70 means 1970, 00 means 2000 and 69 means 2069. I advise you to always use the 4-digit year. So, a column in your database table can receive only years.
Case of Type
When creating a table, the data type (date and/or time) can be written in any case (lower or upper), and not just uppercase, as I have illustrated in this part of the series.
Well, you should have learned something in this part of the series. We take a break at this point and continue in the next part of the series with other relevant issues.
Chrys