Thứ Ba, Tháng Hai 11, 2025
spot_img
HomeDATE/TIME processing function in SQL - Part 1

DATE/TIME processing function in SQL – Part 1

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

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

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisment -
Google search engine

Most Popular

Recent Comments