sql - SQL 命令中的 TIMESTAMP 和 CURRENT_TIMESTAMP 问题

Question

我在创建使用 CURRENT_TIMESTAMP 指定日期的表时遇到一些问题。我需要这个，因为我正在使用 java jpa 实体按日期检索它们。如果我运行到本地 h2 数据库，我没有任何问题。

在此示例中:

INSERT INTO Post (id, title, slug, teaser, body, author_id, posted_on)
VALUES (1, 'Spring Boot Rocks!', 'spring-boot-rocks', @TEASER, @BODY, 1, CURRENT_TIMESTAMP);

一切都已创建并完美运行，但是当我在要连接的 Azure SQL 数据库中尝试相同的查询时，出现错误

Failed to execute query. Error: Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

如果我尝试将 CURRENT_TIMESTAMP 更改为 TIMESTAMP 我得到；

Failed to execute query. Error: Invalid column name 'TIMESTAMP'. If I change it to DEFAULT as the previous error suggests the tables get created but I can't retrieve them by date of creation since DEFAULT is not a time value.

完整查询

SET IDENTITY_INSERT author ON

insert into author(id,first_name,last_name,email) values (1,'Dan','Vega','<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="086c69667e6d6f69486f65696164266b6765" rel="noreferrer noopener nofollow">[email protected]</a>');
insert into author(id,first_name,last_name,email) values (2,'John','Smith','<a href="https://stackoverflow.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="95fffafdfbe6f8fce1fdd5f2f8f4fcf9bbf6faf8" rel="noreferrer noopener nofollow">[email protected]</a>');

SET IDENTITY_INSERT author OFF
SET IDENTITY_INSERT post ON

DECLARE @TEASER varchar(4000) = 'text...'
DECLARE @BODY varchar(4000) = 'text...'

insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (1,'Spring Boot Rocks!','spring-boot-rocks',@TEASER,@BODY,1,CURRENT_TIMESTAMP);
insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (2,'Spring Data Rocks!','spring-data-rocks',@TEASER,@BODY,1,CURRENT_TIMESTAMP);
insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (3,'John Blog Post 1','john-blog-post-1',@TEASER,@BODY,2,CURRENT_TIMESTAMP);
insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (4,'John Blog Post 2','john-blog-post-2',@TEASER,@BODY,2,CURRENT_TIMESTAMP);
insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (5,'John Blog Post 3','john-blog-post-3',@TEASER,@BODY,2,CURRENT_TIMESTAMP);
insert into Post(id,title,slug,teaser,body,author_id,posted_on) values (6,'Refactoring our Spring Data Project','refactoring-spring-data-project',@TEASER,@BODY,1,CURRENT_TIMESTAMP);

SET IDENTITY_INSERT post OFF

Answer 1

SQL Server(和 SQL Azure)中的 TIMESTAMP 数据类型不是 ISO/ANSI 标准定义的(这是 SQL Server 原始 Sybase 遗产的遗留物) )。

它实际上只是一个用于乐观并发检查的二进制计数器 - 它与日期和/或时间完全没有关系!

要存储和处理日期和时间，请使用数据类型 DATE(仅适用于日期 - 无时间)或 DATETIME2(n) 表示日期和时间