首页 > 其他分享 >A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (

A DATETIME or TIMESTAMP value can include a trailing fractional seconds part in up to microseconds (

时间:2024-02-27 10:25:33浏览次数:27  
标签:digits 10 00 01 seconds TIMESTAMP DATETIME values

MySQL :: MySQL 8.0 Reference Manual :: 13.2.2 The DATE, DATETIME, and TIMESTAMP Types https://dev.mysql.com/doc/refman/8.0/en/datetime.html

13.2.2 The DATE, DATETIME, and TIMESTAMP Types

The DATEDATETIME, and TIMESTAMP types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATEDATETIME, and TIMESTAMP values in several formats, described in Section 11.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.

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.499999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.499999'. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 13.2.6, “Fractional Seconds in Time Values”.

The TIMESTAMP and DATETIME data types offer automatic initialization and updating to the current date and time. For more information, see Section 13.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. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 7.1.15, “MySQL Server Time Zone Support”.

In MySQL 8.0.19 and later, you can specify a time zone offset when inserting a TIMESTAMP or DATETIME value into a table. See Section 11.1.3, “Date and Time Literals”, for more information and examples.

Invalid DATEDATETIME, or TIMESTAMP values are converted to the “zero” value of the appropriate type ('0000-00-00' or '0000-00-00 00:00:00'), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 7.1.11, “Server SQL Modes”.

In MySQL 8.0.22 and later, you can convert TIMESTAMP values to UTC DATETIME values when retrieving them using CAST() with the AT TIME ZONE operator, as shown here:

mysql> SELECT col,
     >     CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut
     >     FROM ts ORDER BY id;
+---------------------+---------------------+
| col                 | ut                  |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
| 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
| 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
+---------------------+---------------------+

For complete information regarding syntax and additional examples, see the description of the CAST() function.

Be aware of certain properties of date value interpretation in MySQL:

  • MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as '10:11:12' might look like a time value because of the :, but is interpreted as the year '2010-11-12' if used in date context. The value '10:45:15' is converted to '0000-00-00' because '45' is not a valid month.

    The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.

  • The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enable ALLOW_INVALID_DATES. See Section 7.1.11, “Server SQL Modes”, for more information.

  • MySQL does not accept TIMESTAMP values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value '0000-00-00 00:00:00', if the SQL mode permits this value. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE SQL mode are enabled; see Section 7.1.11, “Server SQL Modes”.

  • Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:

    • Year values in the range 00-69 become 2000-2069.

    • Year values in the range 70-99 become 1970-1999.

    See also Section 13.2.9, “2-Digit Years in Dates”.

 

标签:digits,10,00,01,seconds,TIMESTAMP,DATETIME,values
From: https://www.cnblogs.com/papering/p/18036301

相关文章

  • [MYSQL] MYSQL 8解决 Invalid default value for `created_at`(timestamp)
    1问题描述问题背景执行从MYSQL5.7导出,并在MYSQL8.0.33的数据库中执行DDL建表语句时,报Invaliddefaultvaluefor'create_at'...`create_at`timestampNOTNULLDEFAULT'0000-00-0000:00:00'COMMENT'创建时间/createdtime',...2原因分析表面原因:......
  • 无涯教程-setUTCSeconds()函数
    JavascriptdatesetUTCSeconds()方法用于根据世界时(UTC)设置指定时间的秒字段。setUTCSeconds()-语法Date.setUTCSeconds(secondsValue[,msValue])secondsValue  - 0到59之间的整数,代表秒。msValue      - 一个介于0和999之间的数字,代表......
  • 无涯教程-getSeconds()函数
    JavaScriptdategetSeconds()方法根据本地时间返回指定日期中的秒数。getSeconds返回的值是0到59之间的整数。getSeconds()-语法Date.getSeconds()getSeconds()-返回值根据当地时间返回指定日期中的秒数。getSeconds()-示例vardt=newDate("December25,1995......
  • [Flink] Flink源码分析 : BoundedOutOfOrdernessTimestampExtractor
    0序言0.1缘起importorg.apache.flink.api.common.functions.MapFunction;importorg.apache.flink.api.java.tuple.Tuple;importorg.apache.flink.api.java.tuple.Tuple3;importorg.apache.flink.configuration.Configuration;importorg.apache.flink.configuration.......
  • niushop单商户v5多店版升级到v5.3后商业插件报错问题综合解决方式variable type error
    大家可能像我一样遇到一个奇葩问题就是,niushop系统从5.2内核升级到5.3后所有的插件都不能正常使用了,特别是第三方的商业插件,官方给的说法是要重新适配,这个需要较多时间,不过我总结了一下自己就可以修复比如以下插件会遇到这种问题!niushop支付宝小程序插件niushop阿里云插件niushop......
  • SQL中的unix_timestamp()函数
    unix_timestamp()是SQL中用于将日期和时间转换为UNIX时间戳的函数。UNIX时间戳是指从1970年1月1日(UTC)开始的秒数。使用场景:时间戳转换:当你有一个日期和时间,并希望将其转换为UNIX时间戳格式时。数据整合:在数据整合过程中,你可能需要将来自不同源的数据统一到相同的......
  • Flink自定义Assigning Timestamps和Watermarks 使用Scal语言
    Flink自定义AssigningTimestamps和Watermarks使用Scal语言为了让eventtime工作,Flink需要知道事件的时间戳,这意味着流中的每个元素都需要分配其事件时间戳。这个通常是通过抽取或者访问事件中某些字段的时间戳来获取的。时间戳的分配伴随着水印的生成,告诉系统事件时间中的......
  • 记一次 MySQL timestamp 精度问题的排查 → 过程有点曲折
    开心一刻下午正准备出门,跟正刷着手机的老妈打个招呼我:妈,今晚我跟朋友在外面吃,就不在家吃了老妈拿着手机跟我说道:你看这叫朋友骗缅北去了,tm血都抽干了,多危险我:那是他不行,你看要是吴京去了指定能跑回来老妈:还吴京八经的,特么牛魔王去了都得耕地,唐三藏去了都......
  • knex迁移 - 'ER_INVALID_DEFAULT: 'timestamp'的默认值无效
    在Knex中使用.timestamp()方法时,默认情况下会为该列设置当前时间戳作为默认值。然而,根据你的错误提示,数据库可能不支持此默认值。为了解决这个问题,你可以尝试以下方法:如果你的数据库支持DEFAULTCURRENT_TIMESTAMP,你可以尝试在列定义中添加defaultTo(knex.fn.now(......
  • 【Linux】正则匹配SQL里面的时间 TIMESTAMP
    在使用plsql或者dbeaver的insertsql导出的时候通常日期格式的会导出为以下形式,我们通常将这些日期需要更新为sysdate或者to_char(sysdate,'YYYYMMDD')的形式,此时可以使用正则匹配来替换,以下列举了常见的两种时间场景:1.匹配TIMESTAMP'2023-12-2318:00:01:000000'通常创建时......