Dates play an important role in the data.
Few scenarios where Date and Time are involved with table:
To work with the dates, we should have a good understanding of Date and DateTime data types.
For. e.g. Taking today’s date and subtracting customer’s date of birth to find age.
Let us check some of the important Date aspects.
1) Check the data types of any table columns:
You can simply check the Create statement of that table. Create statement contains the column names and type of each column along with the Primary key and indexes (if any) present in the table.
SHOW CREATE TABLE tbl_name;
CREATE TABLE `Orders`
( `OrderID` int(11) NOT NULL AUTO_INCREMENT,
`CustomerID` int(11) DEFAULT NULL,
`EmployeeID` int(11) DEFAULT NULL,
`OrderDate` date DEFAULT NULL,
`ShipperID` int(11) DEFAULT NULL,
PRIMARY KEY (`OrderID`)
2) Get Today’s Date and Current Time:
Current_Date() is the function to get today’s date and Now() is the function to get current date and time.
SELECT Current_Date(), Now();
3) Add Date and Time to the column:
Date_ADD() is the function to add days, hours or minutes or seconds to the column
Date_SUB() is the function to subtract days, hours or minutes or seconds from the column
SELECT CURRENT_DATE() as Todays_Date,
DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) as Yesterdays_Date,
DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY) as Tomorrows_Date,
NOW() as CurrentTime,
DATE_SUB(NOW(), INTERVAL 1 HOUR) as Past_1_hour,
DATE_ADD(NOW(), INTERVAL 3 HOUR) as Later_3_hour,
DATE_SUB(NOW(), INTERVAL 30 MINUTE) as Past_30_mins;
4) Calculating days difference between two dates:
Suppose a column with name “DOB” in table contains the date of birth of users and you want to calculate the age from dob. In this case, you can use DATEDIFF function which calculates the no. of days between two dates and later it can be divided by 365 to calculate age in years.
SELECT DATEDIFF(NOW(), DOB)/365 as Age;