What is Datetime in SQL? How to use Date function in SQL? Let's find out with Quantrimang.com!
SQL is a programming language that is no longer unfamiliar to many people. Learning and practicing it is not difficult once you have grasped the basics.
Overall, working with databases is quite simple as users need to know the basic queries and keywords that should be used to retrieve, update and delete data in the database. SQL allows users to store structured data (data in the form of rows and columns) and provides simple queries to work on it. In this article, we will discuss Date and Time Functions in SQL.
Most programmers need to work with date/time data types in SQL. Sometimes, this can be a complex problem that is difficult for an inexperienced SQL programmer to solve. Suppose you have a Products table with a timestamp column. It creates each timeline for each customer order. When processing this table, you may face the following problems:
- Data could not be inserted into the Products table because the application tried to insert data in a different date format.
- Suppose you have data in a table in YYYY-MM-DD hh:mm: ss format. You have a daily revenue report and in it, you want to group the data by date. You want the data in the report to be in YYYY-MM-DD format.
This is just one of many common cases when programming in SQL but does not have the required date format. At this point, it's difficult to change the table properties to meet each condition. In this case you need to know how to use the time function in SQL.
Date is one of the most important functions used in SQL, but for those new to this programming language, it is a bit confusing because there are many date formats that can be stored in the database. database and in different formats the user wants to retrieve it as per requirement. In SQL, DateTime is commonly used to store both date and time values at a time in one column.
Here are the details date or date functions in SQL.
Date/time processing functions
No | JAW | DESCRIBE |
1 | ADDDATE() | Add a time period to date |
2 | ADDTIME() | Add a period to time |
3 | CONVERT_TZ() | Time zone conversion |
4 | CURDATE() | Returns the current date |
5 | CURRENT_DATE() | Returns the current date |
6 | CURRENT_TIME() | Returns the current time |
7 | CURRENT_TIMESTAMP() | Returns the current date and time |
8 | CURTIME() | Returns the current time |
9 | DATE_ADD() | Add time |
10 | DATE_FORMAT() | Format the time value |
11 | DATE_SUB() | Minus time |
12 | DATE() | Returns the date part of a time expression |
13 | DATEDIFF() | Returns the difference between two time values |
14 | DAY() | Returns the order of days in the month (from 0 to 31) |
15 | DAYNAME() | Returns the name of the day of the week |
16 | DAYOFMONTH() | Returns the order of days in the month (from 0 to 31) |
17 | DAYOFWEEK() | Returns the day of the week index (1 = Sunday, 2 = Monday… 7 = Saturday) |
18 | DAYOFYEAR() | Returns the order of days of the year (from 1 to 366) |
19 | EXTRACT | Extract the time value from the original expression |
20 | FROM_DAYS() | Converts a number to a date value |
21 | FROM_UNIXTIME() | Returns the date that represents the original Unixtime parameter |
22 | HOUR() | Returns the hour portion from a time expression |
23 | LAST_DAY() | Returns the corresponding value for the last day of the month |
24 | LOCALTIME() | Returns the current date and time |
25 | LOCALTIMESTAMP() | Returns the current date and time |
26 | MAKEDATE() | Returns a date with the given parameters |
27 | MAKETIME() | Returns the time value with the passed parameters |
28 | MICROSECOND() | Returns microseconds from the original expression |
29 | MINUTE() | Returns the minute value from a time expression |
30 | MONTH() | Returns the month value from a time expression |
31 | MONTHNAME() | Returns the month name from a time expression |
32 | NOW() | Returns the current date and time |
33 | PERIOD_ADD() | Add a period to the month of the year |
34 | PERIOD_DIFF() | Returns the number of months between time periods |
35 | QUARTER() | Returns the quarter value from a time expression |
36 | SEC_TO_TIME() | Convert seconds to 'HH:MM:SS' format |
37 | SECOND() | Returns the seconds value from a time expression |
38 | STR_TO_DATE() | Converts a string to a date |
39 | SUBDATE() | Subtract a period of time from the passed date |
40 | SUBTIME() | Subtract two periods |
41 | SYSDATE() | Returns the current date and time |
42 | TIME_FORMAT() | Format the time value |
43 | TIME_TO_SEC() | Returns the number of seconds converted from the original parameter |
44 | TIME() | Returns the time value from the original expression |
45 | TIMEDIFF() | Returns the difference between two time values |
46 | TIMESTAMP() | Returns datetime expression |
47 | TIMESTAMPADD() | Adds the specified time period to the initial parameter |
48 | TIMESTAMPDIFF() | Returns an integer representing the time difference between two expressions |
49 | TO_DAYS() | Returns the number of days between the time value date and year 0 |
50 | UNIX_TIMESTAMP() | Returns the number of seconds in Unix timestamp from the original expression |
51 | UTC_DATE() | Returns the current UTC date |
52 | UTC_TIME() | Returns the current UTC time |
53 | UTC_TIMESTAMP() | Returns the current UTC date and time |
54 | WEEKDAY() | Returns the day of the week index (0 = Monday, 1 = Tuesday… 6 = Sunday) |
55 | WEEKOFYEAR() | Returns the week index of the year of a time expression |
56 | YEAR() | Returns the corresponding year of the original parameter |
Date data types in SQL
MySQL comes with data types to store date or date/time values in the database:
- DATE – format YYYY-MM-DD
- DATETIME – format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP – format: YYYY-MM-DD HH:MI:SS
- YEAR – YYYY or YY format
With:
- Y is year
- M is the month
- D is day
- H is for hour
- MM is minute
- SS is seconds
YYYY is the year stored as four digits, YY is the year stored as two digits; MM is to store the month as two digits, for example July will be 07):
Track row creation or edit times
While working with the database of a large application, you often need to store the creation time of a record or the last modification in the database, for example, saving the date & time when a user registers, or when a user updates most recent password…
In MySQL, you can use the NOW() function to insert the current timestamp as follows:
-- Syntax for MySQL Database
INSERT INTO users (name, birth_date, created_at)
VALUES ('Bilbo Baggins', '1998-04-16', NOW());
However, if you don't want to manually insert the current date and time, you can just use the auto-initialization and auto-update properties of TIMESTAMP and DATETIME.
To attach the attribute automatically, define the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses in the column definition as follows:
-- Syntax for MySQL Database
CREATE TABLE users (
id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL UNIQUE,
birth_date DATE NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
In the next part, Quantrimang will help you learn how to use all of the above functions with syntax and specific examples, please remember to follow along.
Previous lesson: WILDCARD representative operator in SQL
Next article: DATE/TIME processing function in SQL – Part 2