Mysql中的时间

Date Time format

refer to : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html

Date format

MySQL recognizes DATE values in these formats:

• As a string in either ‘YYYY-MM-DD’ or ‘YY-MM-DD’ format

select DATE'2022-09-27';


+------------------+
| DATE'2022-09-27' |
+------------------+
| 2022-09-27       |
+------------------+
1 row in set, 1 warning (0.00 sec)

DATETIME format

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

• As a string in either ‘YYYY-MM-DD hh:mm:ss’ or ‘YY-MM-DD hh:mm:ss’ format

The date and time parts can be separated by T rather than a space. For example, ‘2012-12-31 11:30:45’ ‘2012-12-31T11:30:45’ are equivalent.

Date and Time Data Type Syntax

refer to : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html

The date and time data types for representing temporal values are DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

MySQL permits fractional seconds for TIME, DATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIME, DATETIME, or TIMESTAMP, and fsp is the fractional seconds precision.

For example:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));

The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)

Any TIMESTAMP or DATETIME column in a table can have automatic initialization and updating properties; see Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

DATE

A date. The supported range is ‘1000-01-01’ to ‘9999-12-31’. MySQL displays DATE values in ‘YYYY-MM-DD’ format, but permits assignment of values to DATE columns using either strings or numbers.

DATETIME[(fsp)]

A date and time combination. The supported range is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’. MySQL displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss[.fraction]’ format, but permits assignment of values to DATETIME columns using either strings or numbers.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

Automatic initialization and updating to the current date and time for DATETIME columns can be specified using DEFAULT and ON UPDATE column definition clauses, as described in Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

TIMESTAMP[(fsp)]

A timestamp. The range is ‘1970-01-01 00:00:01.000000’ UTC to ‘2038-01-19 03:14:07.999999’ UTC. TIMESTAMP values are stored as the number of seconds since the epoch (‘1970-01-01 00:00:00’ UTC). A TIMESTAMP cannot represent the value ‘1970-01-01 00:00:00’ because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing ‘0000-00-00 00:00:00’, the “zero” TIMESTAMP value.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP column definition clauses

TIME[(fsp)]

A time. The range is ‘-838:59:59.000000’ to ‘838:59:59.000000’. MySQL displays TIME values in ‘hh:mm:ss[.fraction]’ format, but permits assignment of values to TIME columns using either strings or numbers.

An optional fsp value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.

YEAR[(4)]

A year in 4-digit format. MySQL displays YEAR values in YYYY format, but permits assignment of values to YEAR columns using either strings or numbers. Values display as 1901 to 2155, or 0000.

The DATE, DATETIME, and TIMESTAMP Types

refer to : https://dev.mysql.com/doc/refman/8.0/en/datetime.html

The DATE, DATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE, DATETIME, and TIMESTAMP values in several formats, described in Section 9.1.3, “Date and Time Literals”. For the DATE and DATETIME range descriptions, “supported” means that although earlier values might work, there is no guarantee.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in ‘YYYY-MM-DD’ format. The supported range is ‘1000-01-01’ to ‘9999-12-31’.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ‘YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME or TIMESTAMP column is stored rather than discarded. With the fractional part included, the format for these values is ‘YYYY-MM-DD hh:mm:ss[.fraction]’, the range for DATETIME values is ‘1000-01-01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’, and the range for TIMESTAMP values is ‘1970-01-01 00:00:01.000000’ to ‘2038-01-19 03:14:07.999999’.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 11.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server’s time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store.

Automatic Initialization and Updating for TIMESTAMP and DATETIME

refer to : https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp).

For any TIMESTAMP or DATETIME column in a table, you can assign the current timestamp as the default value, the auto-update value, or both:

An auto-initialized column is set to the current timestamp for inserted rows that specify no value for the column.

An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).

CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tenant_rule (	`id` BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
	`rule_type` VARCHAR(50) NOT NULL,
	`contain_promo_product` TINYINT(1) NOT NULL,
	`brand` VARCHAR(50) NOT NULL,
	`tenant` VARCHAR(50) NOT NULL,
	`enable` TINYINT(1) NOT NULL,
	`gmt_created` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
	`gmt_modified` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3)
, primary key(`id`) ) Engine=InnoDB;

时间函数

date_add()

DATE_ADD() 函数向日期添加指定的时间间隔。

语法
DATE_ADD(date,INTERVAL expr type)
date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。

type 参数可以是下列值：

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

假设我们有如下的表：

现在，我们希望向 “OrderDate” 添加 2 天，这样就可以找到付款日期。

我们使用下面的 SELECT 语句：

SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders

结果：

再看另一个例子：查询距离当前时间，一个时间范围内的记录

select * from dtm.trans_global where status not in ('succeed', 'failed') and
  create_time between date_add(now(), interval -3600 second) and date_add(now(), interval -120 second);

DATE_FORMAT(date,format)

refer to : https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format

这个函数的作用：将日期，转为字符串

Formats the date value according to the format string. If either argument is NULL, the function returns NULL.

The specifiers shown in the following table may be used in the format string. The % character is required before format specifier characters. The specifiers apply to other functions as well: STR_TO_DATE(), TIME_FORMAT(), UNIX_TIMESTAMP().

usage:

SELECT DATE_FORMAT(trigger_time,'%Y-%m-%d') as everyday FROM iot.t_alarm_record;