1

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','danvega@gmail.com');
insert into author(id,first_name,last_name,email) values (2,'John','Smith','johnsmith@gmail.com');

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
MrSir
  • 576
  • 2
  • 11
  • 29

2 Answers2

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    If i replace `CURRENT_TIMESTAMP` with `DATE` I have `Failed to execute query. Error: Invalid column name 'DATE'.` – MrSir Aug 16 '17 at 17:52
  • @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 ! – marc_s Aug 16 '17 at 17:54
  • 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.' – MrSir Aug 17 '17 at 14:05
  • @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) – marc_s Aug 17 '17 at 14:38
  • 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 Aug 18 '17 at 17:05
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;
MrSir
  • 576
  • 2
  • 11
  • 29