CODEWITHSUNDEEP
sqlazuresql-timestamp
MrSir
MrSir

Reputation: 596

Issues with TIMESTAMP and CURRENT_TIMESTAMP in SQL commands

I have some issue creating tables that use CURRENT_TIMESTAMP to specify a date. I need this because I'm using java jpa entities to retrieve them by date. If I run to a local h2 database I have no issues.

In this example:

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);

Everything gets created and works perfectly, but when I try the same query in an Azure SQL database that I'm connecting into I get the error

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.

If I try to change CURRENT_TIMESTAMP to TIMESTAMP I get;

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.

full query

SET IDENTITY_INSERT author ON

insert into author(id,first_name,last_name,email) values (1,'Dan','Vega','[email protected]');
insert into author(id,first_name,last_name,email) values (2,'John','Smith','[email protected]');

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

Upvotes: 1

Views: 2551

Answers (2)

MrSir
MrSir

Reputation: 596

Fixed by recreating the database using application.properties spring.jpa.hibernate.ddl-auto=create-drop and replacing 

@CreatedDate @Column(columnDefinition = "TIMESTAMP")
private Date postedOn;

to

@CreatedDate @Column(columnDefinition = "DATETIME2(0)")
private Date postedOn;

Upvotes: 0

marc_s
marc_s

Reputation: 755491

The TIMESTAMP datatype in SQL Server (and SQL Azure) is NOT what the ISO/ANSI Standard defines (this is a leftover of the original Sybase heritage of SQL Server).

It's really just a binary counter for optimistic concurrency checking - it has NOTHING to do with a date and/or time at all!

To store and handle dates and times, use the datatype DATE (for just dates - no time) or DATETIME2(n) for date&time instead

Upvotes: 4

Related Questions