Postgres timestamp formatting in query

0
votes

I am migrating a code with its own ORM from Db2 to postgres. I have a query that executes the following sql on postgres 10 -

SELECT * FROM TriggerQueue 
WHERE  TriggerQueue.atServerStartup = 'Y' 
AND (TriggerQueue.scheduledatetime > '2018-06-21 20.02.57.827' OR 
TriggerQueue.scheduleDateTime is null) AND TriggerQueue.inputQueue = 'N'

but the pgadmin is showing the following error:

ERROR:  invalid input syntax for type timestamp: "2018-06-21 
20.02.57.827"
LINE 3: AND (TriggerQueue.scheduledatetime > '2018-06-21 20.02.57.8...
                                             ^
SQL state: 22007

I'm guessing the timestamp format is wrong based on sql state, but I'm not sure how to format the value. Any insight on this would be very helpful.

EDIT : Timestamp field in pg is of the type timestamp without timezone. Pgadmin shows size of 6.

1
postgresql

1 Answers

3
votes

Use a proper timestamp literal:

timestamp '2018-06-21 20:02:57.827'

Note the : to separate hours, minutes and seconds