using timestamp sql

Question

trying to add a row with timestamp for my table which consists of

time timestamp without time zone NOT NULL

I'm using the function now(), but I keep getting

ERROR: invalid input syntax for integer: "2016-12-09 14:32:14.111332"

I tried now()::timestamp didn't help either any idea?

CREATE TABLE game (
   score integer,
   score2 integer,
  time timestamp without time zone NOT NULL,
  CHECK ((score is NULL) and (score is NULL)) 
);

insert into game values(2,3,now()); Thanks!

Use single quotes for (text) literals " '2016-12-09 14:32:14.111332' — joop
– joop, Commented Dec 9, 2016 at 12:41
invalid input syntax for integer What is the integer here ? — joop
– joop, Commented Dec 9, 2016 at 12:45
Try insert into game (score, score2, time) values(2,3,now()); Unrelated, but: time is a horrible name for a column - even worse for a timestamp column. — user330315
– user330315, Commented Dec 9, 2016 at 12:54

iniestar · Accepted Answer · 2016-12-09 13:35:12Z

1

I think your problem resides on the CHECK...

I tested your query with few tweaks and it worked.

Working script:

CREATE TABLE game (
  score integer,
  score2 integer,
  time2 timestamp without time zone NOT NULL,
  CHECK ((score is NOT NULL) and (score2 is NOT NULL)) 
);

Check working example here

P.s As user @Raul mentioned, be careful with variable names to not use reserved words, time may get you into trouble

answered Dec 9, 2016 at 13:35

iniestar

2622 gold badges5 silver badges14 bronze badges

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

Comments

Raul Cuth · Accepted Answer · 2016-12-09 13:09:04Z

0

My best guess is that 2016-12-09 14:32:14.111332 is not recognized as an ISO standard.

ISO 8601 uses the 24-hour clock system. The basic format is [hh][mm][ss] and the extended format is [hh]:[mm]:[ss]. Accepted would be: hh:mm:ss.sss, hh:mm:ss or hh:mm.

What bothers me is all those numbers at the end: 2016-12-09 14:32:14.111332 14.111332, I don't recall seeing timestamps like this, I might be wrong.

Maybe try inserting 2016-12-09 14:32:14 and see if it throws the same error. Also the name of the column should be changed, column with names the same as some keywords can cause statements to fail.

edited Dec 9, 2016 at 13:09

answered Dec 9, 2016 at 12:56

Raul Cuth

2,4791 gold badge15 silver badges18 bronze badges

3 Comments

Mumfordwiz Over a year ago

It's not it. I think i figured it out.. Might be some problem with a function i wrote..

Raul Cuth Over a year ago

Cool, maybe post the answer also, if you find it. Would be helpful for others that have the same problem.

Raul Cuth Over a year ago

Also as an advice, be more careful when naming columns, you might have some trouble inserting in a column with the name date, I think time is also in the same spot.

Collectives™ on Stack Overflow

using timestamp sql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related