search
HomeDatabaseMysql Tutorialmysql之TIMESTAMP(时间戳)用法详解_MySQL

一、TIMESTAMP的变体

TIMESTAMP时间戳在创建的时候可以有多重不同的特性,如:

1.在创建新记录和修改现有记录的时候都对这个数据列刷新:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.在创建新记录的时候把这个字段设置为当前时间,但以后修改时,不再刷新它:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3.在创建新记录的时候把这个字段设置为0,以后修改时刷新它:

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4.在创建新记录的时候把这个字段设置为给定值,以后修改时刷新它:

TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

MySQL目前不支持列的Default 为函数的形式,如达到你某列的默认值为当前更新日期与时间的功能,你可以使用TIMESTAMP列类型,下面就详细说明TIMESTAMP列类型
 
二、TIMESTAMP列类型

TIMESTAMP值可以从1970的某时的开始一直到2037年,精度为一秒,其值作为数字显示。
TIMESTAMP值显示尺寸的格式如下表所示:

+---------------+----------------+| 列类型    | 显示格式    || TIMESTAMP(14) | YYYYMMDDHHMMSS | | TIMESTAMP(12) | YYMMDDHHMMSS  || TIMESTAMP(10) | YYMMDDHHMM   || TIMESTAMP(8) | YYYYMMDD    || TIMESTAMP(6) | YYMMDD     || TIMESTAMP(4) | YYMM      || TIMESTAMP(2) | YY       |+---------------+----------------+

“完整”TIMESTAMP格式是14位,但TIMESTAMP列也可以用更短的显示尺寸,创造最常见的显示尺寸是6、8、12、和14。
你可以在创建表时指定一个任意的显示尺寸,但是定义列长为0或比14大均会被强制定义为列长14。
列长在从1~13范围的奇数值尺寸均被强制为下一个更大的偶数。
 
列如:

定义字段长度   强制字段长度TIMESTAMP(0) -> TIMESTAMP(14)TIMESTAMP(15)-> TIMESTAMP(14)TIMESTAMP(1) -> TIMESTAMP(2)TIMESTAMP(5) -> TIMESTAMP(6)

所有的TIMESTAMP列都有同样的存储大小,使用被指定的时期时间值的完整精度(14位)存储合法的值不考虑显示尺寸。不合法的日期,将会被强制为0存储

这有几个含意:

1.虽然你建表时定义了列TIMESTAMP(8),但在你进行数据插入与更新时TIMESTAMP列实际上保存了14位的数据(包括年月日时分秒),只不过在你进行查询时MySQL返回给你的是8位的年月日数据。如果你使用ALTER TABLE拓宽一个狭窄的TIMESTAMP列,以前被“隐蔽”的信息将被显示。

2.同样,缩小一个TIMESTAMP列不会导致信息失去,除了感觉上值在显示时,较少的信息被显示出。

3.尽管TIMESTAMP值被存储为完整精度,直接操作存储值的唯一函数是UNIX_TIMESTAMP();由于MySQL返回TIMESTAMP列的列值是进过格式化后的检索的值,这意味着你可能不能使用某些函数来操作TIMESTAMP列(例如HOUR()或SECOND()),除非TIMESTAMP值的相关部分被包含在格式化的值中。
例如,一个TIMESTAMP列只有被定义为TIMESTAMP(10)以上时,TIMESTAMP列的HH部分才会被显示,因此在更短的TIMESTAMP值上使用HOUR()会产生一个不可预知的结果。

4.不合法TIMESTAMP值被变换到适当类型的“零”值(00000000000000)。(DATETIME,DATE亦然)

例如你可以使用下列语句来验证:

CREATE TABLE test ('id' INT (3) UNSIGNED AUTO_INCREMENT, 'date1'TIMESTAMP (8) PRIMARY KEY('id'));INSERT INTO test SET id = 1;SELECT * FROM test;+----+----------------+| id | date1     |+----+----------------+| 1 | 20021114    |+----+----------------+ALTER TABLE test CHANGE 'date1' 'date1' TIMESTAMP(14);SELECT * FROM test;+----+----------------+| id | date1     |+----+----------------+| 1 | 20021114093723 |+----+----------------+

你可以使用TIMESTAMP列类型自动地用当前的日期和时间标记INSERT或UPDATE的操作。
如果你有多个TIMESTAMP列,只有第一个自动更新。自动更新第一个TIMESTAMP列在下列任何条件下发生:

1.列值没有明确地在一个INSERT或LOAD DATA INFILE语句中指定。
2.列值没有明确地在一个UPDATE语句中指定且另外一些的列改变值。(注意一个UPDATE设置一个列为它已经有的值,这将不引起TIMESTAMP列被更新,因为如果你设置一个列为它当前的值,MySQL为了效率而忽略更改。)
3.你明确地设定TIMESTAMP列为NULL.
4.除第一个以外的TIMESTAMP列也可以设置到当前的日期和时间,只要将列设为NULL,或NOW()。

CREATE TABLE test ( 'id' INT (3) UNSIGNED AUTO_INCREMENT,'date1' TIMESTAMP (14),'date2' TIMESTAMP (14),PRIMARY KEY('id'));INSERT INTO test (id, date1, date2) VALUES (1, NULL, NULL);INSERT INTO test SET id= 2;+----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 1 | 20021114093723 | 20021114093723 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

第一条指令因设date1、date2为NULL,所以date1、date2值均为当前时间第二条指令因没有设date1、date2列值,第一个TIMESTAMP列date1为更新为当前时间,而二个TIMESTAMP列date2因日期不合法而变为“00000000000000”

UPDATE test SET id= 3 WHERE id=1;+----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 3 | 20021114094009 | 20021114093723 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

这条指令没有明确地设定date2的列值,所以第一个TIMESTAMP列date1将被更新为当前时间

UPDATE test SET id= 1,date1=date1,date2=NOW() WHERE id=3; +----+----------------+----------------+| id | date1     | date2     |+----+----------------+----------------+| 1 | 20021114094009 | 20021114094320 || 2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

这条指令因设定date1=date1,所以在更新数据时date1列值并不会发生改变而因设定date2=NOW(),所以在更新数据时date2列值会被更新为当前时间此指令等效为:

UPDATE test SET id= 1,date1=date1,date2=NULL WHERE id=3;

因MySQL返回的 TIMESTAMP 列为数字显示形式,你可以用DATE_FROMAT()函数来格式化 TIMESTAMP 列,如下所示:

SELECT id,DATE_FORMAT(date1,'%Y-%m-%d %H:%i:%s') As date1,DATE_FORMAT(date2,'%Y-%m-%d %H:%i:%s') As date2 FROM test;+----+---------------------+---------------------+| id | date1        | date2        |+----+---------------------+---------------------+| 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 || 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 |+----+---------------------+---------------------+SELECT id,DATE_FORMAT(date1,'%Y-%m-%d') As date1,DATE_FORMAT(date2,'%Y-%m-%d') As date2 FROM test;+----+-------------+-------------+| id | date1    | date2    |+----+-------------+-------------+| 1 | 2002-11-14 | 2002-11-14 || 2 | 2002-11-14 | 0000-00-00 |+----+-------------+-------------+

在某种程度上,你可以把一种日期类型的值赋给一个不同的日期类型的对象。
然而,而尤其注意的是:值有可能发生一些改变或信息的损失:
 
1.如果你将一个DATE值赋给一个DATETIME或TIMESTAMP对象,结果值的时间部分被设置为'00:00:00',因为DATE值中不包含有时间信息。  
2.如果你将一个DATETIME或TIMESTAMP值赋给一个DATE对象,结果值的时间部分被删除,因为DATE类型不存储时间信息。
3.尽管DATETIME, DATE和TIMESTAMP值全都可以用同样的格式集来指定,但所有类型不都有同样的值范围。

例如,TIMESTAMP值不能比1970早,也不能比2037晚,这意味着,一个日期例如'1968-01-01',当作为一个DATETIME或DATE值时它是合法的,但它不是一个正确TIMESTAMP值!并且如果将这样的一个对象赋值给TIMESTAMP列,它将被变换为0。   
 
三、当指定日期值时,当心某些缺陷:

1.允许作为字符串指定值的宽松格式能被欺骗。例如,因为“:”分隔符的使用,值'10:11:12'可能看起来像时间值,但是如果在一个日期中使用,上下文将作为年份被解释成'2010-11-12'。值'10:45:15'将被变换到'0000-00-00',因为'45'不是一个合法的月份。
 
2.以2位数字指定的年值是模糊的,因为世纪是未知的。MySQL使用下列规则解释2位年值:
在00-69范围的年值被变换到2000-2069。 在范围70-99的年值被变换到1970-1999。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.