Issues with TIMESTAMP and CURRENT_TIMESTAMP in SQL commands

Question

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

marc_s · Accepted Answer · 2017-08-16 16:00:50Z

4

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

answered Aug 16, 2017 at 16:00

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MrSir Over a year ago

If i replace CURRENT_TIMESTAMP with DATE I have Failed to execute query. Error: Invalid column name 'DATE'.

marc_s Over a year ago

@MrSir: you don't have to replace CURRENT_TIMESTAMP with anything - you need to change the datatype of the posted_on column in the table (from TIMESTAMP to e.g. DATETIME2(0)) that you're inserting the data into !

MrSir Over a year ago

I tried to change it with the query ALTER TABLE post ALTER COLUMN posted_on DATETIME2(0); I have 'Cannot alter column 'posted_on' because it is timestamp.'

marc_s Over a year ago

@MrSir: in that case, try to create a new column ALTER TABLE post ADD posted_on2 DATETIME2(0); and then change your INSERT statement to insert into that new column (and leave the old posted_on alone)

MrSir Over a year ago

In the end i recreated the database using spring.jpa.hibernate.ddl-auto=create-drop from application properties and changed the postedOn annotation in the java entity class.

MrSir · Accepted Answer · 2017-08-18 17:07:09Z

0

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;

answered Aug 18, 2017 at 17:07

MrSir

5964 gold badges14 silver badges29 bronze badges

Collectives™ on Stack Overflow

Issues with TIMESTAMP and CURRENT_TIMESTAMP in SQL commands

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related