Working with DateTime in MySQL

MySQL DateTime Tutorial

 
In this tutorial, I am going to explain about MySQL DATE and TIME functions with examples. 
 
DATETIME is used to store the value of both the date and time. By default, DATETIME values ranges from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. It uses the 5 bytes for storage.
 
Syntax for DATETIME format:
 
YYYY-MM-DD HH:MM:SS
 

MySQL DATE and TIME Types

 
There are a number of useful date and time functions in MySQL. I've seen too many applications performing date calculations at the code level when the same can be done using built-in MySQL functions. Before we launch into the functions, however, let's refresh our memory and look at which date and time types are available to MySQL.
 
DATETIME
YYYY-MM-DD HH:MM:SS
DATE
YYYY-MM-DD
TIMESTAMP
YYYYMMDDHHSSMM
TIME
HH:MM:SS
YEAR
YYYY
 

MySQL DATETIME

 
MySQL is quite lenient in how it reads date formats. Although it is wise to use the convention, you can use any other punctuation character you like. For example, now create a table and insert the dummy data as follows.
  1. CREATE TABLE vatsa(Dt DATETIME);    
Now, insert the data in the right format as follows.
  1. INSERT INTO vatsa(Dt) VALUES('2020-09-14 23:18:17');  
Note:
 
Instead of the right format, you can use '=' and '+' signs, as follows:
  1. INSERT INTO vatsa(Dt) VALUES('2020=09=14 23*18*21');   
But although I'm sure they exist, I haven't come across a good reason to use this, so I suggest you keep to the conventions unless absolutely necessary.
 
Now, execute the following query to see the result.
  1. SELECT * FROM vatsa; 
 

SIMPLE DATE and TIME CALCULATION

 
Date calculations are relatively easy. The first function we're going to look at is the YEAR() function, which returns a year from a given date. To get the value of the year, month, week, day, quarter, date, time, hour, minute, and second from a DATETIME value, you use the functions as shown in the following statement:
 
For example:
  1. SELECT YEAR('2020-09-14 23:18:17'AS Year,  
  2.     Month('2020-09-14 23:18:17'AS Month,  
  3.     Day('2020-09-14 23:18:17'AS Day,  
  4.     Hour('2020-09-14 23:18:17'AS Hour,  
  5.     Minute('2020-09-14 23:18:17'AS Minute,  
  6.     Second('2020-09-14 23:18:17'AS Second,  
  7.     Date('2020-09-14 23:18:17'AS Date,  
  8.     Time('2020-09-14 23:18:17'AS Time,  
  9.     Quarter('2020-09-14 23:18:17'AS Quarter; 
 
Alternatively, the user can use a dynamic method to fetch the date and time of the current datetime.
  1. SET @vatsaDATETIME = NOW();  
  2.   
  3. SELECT YEAR(@vatsaDATETIME) AS Year,  
  4.     Month(@vatsaDATETIME) AS Month,  
  5.     Day(@vatsaDATETIME) AS Day,  
  6.     Hour(@vatsaDATETIME) AS Hour,  
  7.     Minute(@vatsaDATETIME) AS Minute,  
  8.     Second(@vatsaDATETIME) AS Second,  
  9.     Date(@vatsaDATETIME) AS Date,  
  10.     Time(@vatsaDATETIME) AS Time,  
  11.     Quarter(@vatsaDATETIME) AS Quarter; 
 

BASIC ARITHMETIC OPERATIONS ON DATE FUNCTION

 
We can perform simple arithmetic operations on date using the '+' and '-' operators. Lets us see some basic examples.
 
For example:
 
1)  To find out which year is five years ahead of a given date, you can use it.
  1. SET @vatsa = now();  
  2. SELECT @vatsa + 5; 
2)  To find out which year was five years in the past, use.
  1. SET @vatsa = now();  
  2. SELECT @vatsa – 5; 
Now, let’s discuss a basic real-time example.
  1. SET @vatsa = now();  
  2. SELECT @vatsa - 5 AS PAST,  
  3.     year(@vatsa) AS CURRENT,  
  4.     @vatsa + 5 AS FUTURE; 
 

NOW() Function

 
Of course, you don't have to hard-code the date. MySQL is quite capable of telling the date and time, using the NOW() function. And, on the execution of this statement, it returns the current date and time.
  1. SELECT NOW();   
 

CURRENT_DATE() Function

 
Or just the date with the CURRENT_DATE() function. And, on the execution of this statement, it returns the current date.
  1. SELECT CURRENT_DATE(); 
 

MySQL DATETIME vs TIMESTAMP

 
TIMESTMP is similar to DATTIME in MySQL. TIMESTAMP requires 4 bytes but DATETIME requires 5 bytes.
 
The TIMESTAMP column stores the full 14 characters, but you can display it in different ways. If you define the column as TIMESTAMP(2), for example, only the two-digit year will be displayed, but the full value is stored. If you later decide to display the full value, you can change the table definition, and the full value will appear.
 
Below is a list of various ways to define a TIMESTAMP, and the resultant display.
 
TIMESTAMP(14)
YYYYMMDDHHMMSS
TIMESTAMP(12)
YYMMDDHHMMSS
TIMESTAMP(10)
YYMMDDHHMM
TIMESTAMP(8)
YYYYMMDD
TIMESTAMP(6)
YYMMDD
TIMESTAMP(4)
YYMM
TIMESTAMP(2)
YY
 

CONCLUSION

 
In this article, I have discussed the concept of DATE and TIME functions in MySQL with various examples.
 
I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 
 
Thanks for reading this article!