PostgreSQL: Problems inserting timestamp into table

[JD]

Hi there everyone,

I am using Zeos 7.2.1rc and I have a serious problem that has been bugging me for over two weeks. I am using NewPascal mORMot server to update a PostgreSQL 9.5 database. I've even used the same mORMot server to update another PostgreSQL 10 database and everything is fine so far.

However a serious problem has arisen. I am trying to update a table with timestamp fields. I want to insert a value like 2017-12-11T15:30:00 into the table.

The client side is sending JSON data from a TV Planit module to the server.

A) In the old version, the Indy 10 server side code is based on SQLDb and is shown below:

Code: Pascal  [Select][+][-]

1.     with dm.qryWrite do
2.     begin
3.       // A. INSERT & EDIT EVENTS
4.       //
5.       for intCount := 0 to Pred(intRows) do
6.       begin
7.         // Clear the SQL text
8.         SQL.Clear;
9.         //
10.         if VariantToUTF8(vJSEvents.Value(intCount).UserField0) = EmptyStr then
11.         begin
12.           SQL.Text := Format(SQLInsertPlanningEvent, [SCHEMA]);
13.           Prepare;
14.         end
15.         else
16.         begin
17.           SQL.Text := Format(SQLUpdatePlanningEvent, [SCHEMA]);
18.           Prepare;
19.           ParamByName('tvp_event_id').AsInteger    := StrToInt(vJSEvents.Value(intCount).UserField0);   // RemoteID in the database
20.         end;
21.         // Copy the parameters. NOTE: NULL values are empty strings in the JSON string sent by the client
22.         //
23.         if VariantToUTF8(vJSIDs.Value(0).ID2) <> EmptyStr then
24.           ParamByName('historique_emploi_id').AsInteger := StrToInt(vJSIDs.Value(0).ID2)
25.         else
26.           ParamByName('historique_emploi_id').Clear;
27.         //
28.         if VariantToUTF8(vJSEvents.Value(intCount).ResourceID) <> EmptyStr then
29.           ParamByName('tvp_resource_id').AsInteger := StrToInt(vJSEvents.Value(intCount).ResourceID)
30.         else
31.           ParamByName('tvp_resource_id').Clear;
32.         //  "StartTime": "2017-06-06T12:00:00"
33.         if VariantToUTF8(vJSEvents.Value(intCount).StartTime) <> EmptyStr then
34.           //ParamByName('debut').AsDateTime := DateAsDMY(StringReplace(vJSEvents.Value(intCount).StartTime, 'T', ' ', [rfReplaceAll]), '-')
35.           ParamByName('debut').AsDateTime := SQLDateTimeToDateTime(StringReplace(vJSEvents.Value(intCount).StartTime, 'T', ' ', [rfReplaceAll]))
36.         else
37.           ParamByName('debut').Clear;
38.         // "EndTime": "2017-06-06T15:00:00"
39.         if VariantToUTF8(vJSEvents.Value(intCount).EndTime) <> EmptyStr then
40.           //ParamByName('fin').AsDateTime := DateAsDMY(StringReplace(vJSEvents.Value(intCount).EndTime, 'T', ' ', [rfReplaceAll]), '-')
41.           ParamByName('fin').AsDateTime := SQLDateTimeToDateTime(StringReplace(vJSEvents.Value(intCount).EndTime, 'T', ' ', [rfReplaceAll]))
42.         else
43.           ParamByName('fin').Clear;
44.         //
45.         if VariantToUTF8(vJSEvents.Value(intCount).Description) <> EmptyStr then
46.           ParamByName('description').AsString := VariantToUTF8(vJSEvents.Value(intCount).Description)
47.         else
48.           ParamByName('description').Clear;
49.         //
50.         if VariantToUTF8(vJSEvents.Value(intCount).Location) <> EmptyStr then
51.           ParamByName('lieu').AsString := VariantToUTF8(vJSEvents.Value(intCount).Location)
52.         else
53.           ParamByName('lieu').Clear;
54.         //
55.         if VariantToUTF8(vJSEvents.Value(intCount).Notes) <> EmptyStr then
56.           ParamByName('commentaires').AsString := VariantToUTF8(vJSEvents.Value(intCount).Notes)
57.         else
58.           ParamByName('commentaires').Clear;
59.         //
60.         if VariantToUTF8(vJSEvents.Value(intCount).Category) <> EmptyStr then
61.           ParamByName('categorie').AsInteger := StrToInt(vJSEvents.Value(intCount).Category)
62.         else
63.           ParamByName('categorie').Clear;
64.         //  TO DO!
65.         if VariantToUTF8(vJSEvents.Value(intCount).AllDayEvent) <> EmptyStr then
66.           ParamByName('alldayevent').AsBoolean := StrToBool(vJSEvents.Value(intCount).AllDayEvent)
67.         else
68.           ParamByName('alldayevent').Clear;
69.         //
70.         if VariantToUTF8(vJSEvents.Value(intCount).DingPath) <> EmptyStr then
71.           ParamByName('dingpath').AsString := VariantToUTF8(vJSEvents.Value(intCount).DingPath)
72.         else
73.           ParamByName('dingpath').Clear;
74.         //
75.         if VariantToUTF8(vJSEvents.Value(intCount).AlarmSet) <> EmptyStr then
76.           ParamByName('alarmset').AsBoolean := StrToBool(vJSEvents.Value(intCount).AlarmSet)
77.         else
78.           ParamByName('alarmset').Clear;
79.         //
80.         if VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvance) <> EmptyStr then
81.           ParamByName('alarmadvance').AsInteger := StrToInt(vJSEvents.Value(intCount).AlarmAdvance)
82.         else
83.           ParamByName('alarmadvance').Clear;
84.         //
85.         if VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvanceType) <> EmptyStr then
86.           ParamByName('alarmadvancetype').AsInteger := StrToInt(vJSEvents.Value(intCount).AlarmAdvanceType)
87.         else
88.           ParamByName('alarmadvancetype').Clear;
89.         // SQLDateTimeToDateTime
90.         if (Trim(VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime)) <> EmptyStr) and (vJSEvents.Value(intCount).SnoozeTime <> null) then
91.           ParamByName('snoozetime').AsDateTime := SQLDateTimeToDateTime(vJSEvents.Value(intCount).SnoozeTime)
92.         else
93.           ParamByName('snoozetime').Clear;
94.         //
95.         if VariantToUTF8(vJSEvents.Value(intCount).RepeatCode) <> EmptyStr then
96.           ParamByName('repeatcode').AsInteger := StrToInt(vJSEvents.Value(intCount).RepeatCode)
97.         else
98.           ParamByName('repeatcode').Clear;
99.         // SQLDateTimeToDateTime
100.         if (Trim(VariantToUTF8(vJSEvents.Value(intCount).RepeatRangeEnd)) <> EmptyStr) and (vJSEvents.Value(intCount).RepeatRangeEnd <> null) then
101.           ParamByName('repeatrangeend').AsDateTime := SQLDateTimeToDateTime(vJSEvents.Value(intCount).RepeatRangeEnd)
102.         else
103.           ParamByName('repeatrangeend').Clear;
104.         //
105.         if VariantToUTF8(vJSEvents.Value(intCount).CustomInterval) <> EmptyStr then
106.           ParamByName('custominterval').AsInteger := StrToInt(vJSEvents.Value(intCount).CustomInterval)
107.         else
108.           ParamByName('custominterval').Clear;
109.  
110.         // Update the database
111.         ExecSQL;
112.       end;    // for intCount := 0 to Pred(intRows) do
113.  

The SQLDateTimeToDateTime function I wrote is as defined below:

Code: Pascal  [Select][+][-]

1. function SQLDateTimeToDateTime(const SQLDateTime: string): TDateTime;
2. begin
3.   Result :=
4.       EncodeDateTime(
5.         SysUtils.StrToInt(Copy(SQLDateTime, 1, 4)),
6.         SysUtils.StrToInt(Copy(SQLDateTime, 6, 2)),
7.         SysUtils.StrToInt(Copy(SQLDateTime, 9, 2)),
8.         SysUtils.StrToInt(Copy(SQLDateTime, 12, 2)),
9.         SysUtils.StrToInt(Copy(SQLDateTime, 15, 2)),
10.         0, 0
11.       );
12. end;
13.  

This worked very well.

B) I just changed the server to a mORMot server using Zeos to save data to the PostgreSQL database. The most recent is the following, except that it is not working because of the timestamp fields, debut, fin and snoozetime. The error message says SQL Error: ERROR: invalid input syntax for type timestamp: " "

Code: Pascal  [Select][+][-]

1.  aServer.fDbProps.ExecuteNoResult(Format(
2.          'INSERT INTO %s.tvp_event (' +
3.             'tvp_resource_id, historique_emploi_id, ' +
4.             'debut, ' +
5.             'fin, ' +
6.             'description, lieu, commentaires, categorie, alldayevent, dingpath, ' +
7.             'alarmset, alarmadvance, alarmadvancetype, snoozetime, repeatcode, ' +
8.             'repeatrangeend, custominterval) ' +
9.           'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ',
10.                [Schema]), [VariantToUTF8(vJSEvents.Value(intCount).ResourceID),
11.                 VariantToUTF8(vJSIDs.contrat_id),
12.                 VariantToUTF8(vJSEvents.Value(intCount).StartTime),
13.                 VariantToUTF8(vJSEvents.Value(intCount).EndTime),
14.                 VariantToUTF8(vJSEvents.Value(intCount).Description),
15.                 VariantToUTF8(vJSEvents.Value(intCount).Location),
16.                 VariantToUTF8(vJSEvents.Value(intCount).Notes),
17.                 VariantToUTF8(vJSEvents.Value(intCount).Category),
18.                 VariantToUTF8(vJSEvents.Value(intCount).AllDayEvent),
19.                 VariantToUTF8(vJSEvents.Value(intCount).DingPath),
20.                 VariantToUTF8(vJSEvents.Value(intCount).AlarmSet),
21.                 VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvance),
22.                 VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvanceType),
23.                 iif(VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime) = EmptyStr, null, VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime)),
24.                 VariantToUTF8(vJSEvents.Value(intCount).RepeatCode),
25.                 VariantToUTF8(vJSEvents.Value(intCount).RepeatRangeEnd),
26.                 VariantToUTF8(vJSEvents.Value(intCount).CustomInterval);
27.  

The client side code has not changed since it does not know what type of server it is talking to. It just sends/receives JSON data with remote REST servers.

Thanks a lot for your assistance.

JD

[JD]

Any ideas anyone? 

[tudi_x]

hi,
not quite related but i was using the below approach:
a. app inserts with unix timestamp
b. in case i need other timestamp i would use postgres time function to insert (works all the time if you do not have different time zones)

[JD]

hi,
not quite related but i was using the below approach:
a. app inserts with unix timestamp
b. in case i need other timestamp i would use postgres time function to insert (works all the time if you do not have different time zones)

Interesting idea. Do you have code you can share with me? Are you refering to the PostgreSQL to_timestamp function?

JD

[tudi_x]

to_timestamp, yes.
https://www.postgresql.org/docs/9.1/static/datatype-datetime.html  section 8.5.1.3. Time Stamps.

no code, sorry, moved to other areas now in a different company.
i was using only the unix timestamp for GUI.
postgres value only for debugging.
a unix timestamp with nanoseconds instead of seconds would come handy.