Sql 简明教程
SQL - Date & Time
SQL 提供多种数据类型和函数来处理数据库中的 Date and Time 值。这是因为日期和时间值以各种格式表示。例如，有两种表示日期值的方法： DD/MM/YYYY 和 MM/DD/YYYY 。类似地，有多种表示时间值的方法。
SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values.
为了让数据库识别以任何格式给出的此类数据，我们使用多种数据类型和函数。
For a database to recognize such data given in any format, we make use of multiple datatypes and functions.
不同的数据库系统使用不同的数据类型和函数来存储和处理日期和时间数据。
Different database systems use different datatypes and functions to store and handle the Date and Time data.
Date & Time Datatypes in SQL
日期和时间数据类型用于 SQL，以多种格式存储日期和时间值。SQL 中可用的数据类型如下所示。
Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed below.
S.No. |
Datatype & Description |
Storage |
1 |
datetime It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds; with the format: YYYY-MM-DD HH:MI:SS. |
8 bytes |
2 |
datetime2 It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
6 - 8 bytes |
3 |
smalldatetime It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. It is stored in the format of YYYY-MM-DD HH:MI:SS. |
4 bytes |
4 |
date It stores date only from January 1, 0001 to December 31 9999, in the format: YYYY-MM-DD. |
3 bytes |
5 |
time It store time only to an accuracy of 100 nanoseconds. |
3 - 5 bytes |
6 |
datetimeoffset It is the same of the datetime2 with the addition of the time zone offset. |
8 - 10 bytes |
7 |
timestamp It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable. |
Example
在下面的示例中，让我们创建一个名为SALES_DETAILS的表，该表只接受不同格式的日期和时间值。
In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.
CREATE TABLE SALES_DETAILS(
orderDate DATE,
shippingDate DATETIME,
deliveredDate TIMESTAMP,
time TIME
);若要将值插入到此表中，请使用以下查询−
To insert values into this table, use the following query −
INSERT INTO SALES_DETAILS VALUES
('2023-02-01', '2023-02-01 :10:00','2023-02-03 :18:00', '18:00');Date & Time Functions in SQL
SQL还提供了许多函数来处理日期和时间值。
SQL also provides multiple functions to handle date and time values.
例如，有不同的函数以不同的格式检索当前时间戳。让我们在下面看看其中的一些函数−
For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below −
CURDATE() Function
若要获取当前日期，我们在MySQL中使用CURDATE()函数。结果日期的格式将是“YYYY-MM-DD”（字符串）或YYYYMMMDD（数字）。
To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be 'YYYY-MM-DD' (string) or YYYYMMMDD (numeric).
SELECT CURDATE();Output
当执行以上查询时，我们获取当前日期−
When we execute the above query, we get the current days date −
CURDATE() |
2023-08-22 |
NOW() Function
MySQL NOW()函数会根据上下午检索当前日期和时间值作为时间戳，且返回的值将采用以下两种格式之一：“YYYY-MM-DD hh:mm:ss”和“YYYYMMDDhhmmss”。
The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: 'YYYY-MM-DD hh:mm:ss' and 'YYYYMMDDhhmmss'.
SELECT NOW();Output
当执行以上的SQL查询时，我们获取带时间的当前日期，如下所示−
When we execute the above SQL query, we get the current date with time as follow −
NOW() |
2023-08-22 15:30:25 |
CURRENT_TIMESTAMP() Function
MySQL CURRENT_TIMESTAMP()函数用于获取当前时间戳。所返回的值将采用“YYYY-MM-DD hh:mm:ss”（字符串）或YYYYMMDDhhmmss（数字）格式。此函数是NOW()的同义词。
The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in 'YYYY-MM-DD hh:mm:ss' (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW().
SELECT CURRENT_TIMESTAMP();