PostgreSQL: Problems inserting timestamp into table lazarus / 16-Databases ---------------------------------------------------------------------- JD · 2017-12-18 10:22 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i     with dm.qryWrite do    begin      // A. INSERT & EDIT EVENTS      //      for intCount := 0 to Pred(intRows) do      begin        // Clear the SQL text        SQL.Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).UserField0) = EmptyStr then      begin          SQL.Text := Format(SQLInsertPlanningEvent, [SCHEMA]);          Prepare;        end        else        begin        SQL.Text := Format(SQLUpdatePlanningEvent, [SCHEMA]);          Prepare;          ParamByName('tvp_event_id').AsInteger    := StrToInt(vJSEvents.Value(intCount).UserField0);   // RemoteID in the database        end;        // Copy the parameters. NOTE: NULL values are empty strings in the JSON string sent by the client        //      if VariantToUTF8(vJSIDs.Value(0).ID2) <> EmptyStr then          ParamByName('historique_emploi_id').AsInteger := StrToInt(vJSIDs.Value(0).ID2)        else          ParamByName('historique_emploi_id').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).ResourceID) <> EmptyStr then        ParamByName('tvp_resource_id').AsInteger := StrToInt(vJSEvents.Value(intCount).ResourceID)        else          ParamByName('tvp_resource_id').Clear;        //  "StartTime": "2017-06-06T12:00:00"        if VariantToUTF8(vJSEvents.Value(intCount).StartTime) <> EmptyStr then        //ParamByName('debut').AsDateTime := DateAsDMY(StringReplace(vJSEvents.Value(intCount).StartTime, 'T', ' ', [rfReplaceAll]), '-')          ParamByName('debut').AsDateTime := SQLD ateTimeToDateTime(StringReplace(vJSEvents.Value(intCount).StartTime, 'T', ' ', [rfReplaceAll]))        else          ParamByName('debut').Clear;        // "EndTime": "2017-06-06T15:00:00"        if VariantToUTF8(vJSEvents.Value(intCount).EndTime) <> EmptyStr then        //ParamByName('fin').AsDateTime := DateAsDMY(StringReplace(vJSEvents.Value(intCount).EndTime, 'T', ' ', [rfReplaceAll]), '-')          ParamByName('fin').AsDateTime := SQLDateTimeToDateTime(StringReplace(vJSEvents.Value(intCount).EndTime, 'T', ' ', [rfReplaceAll]))        else          ParamByName('fin').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).Description) <> EmptyStr then          ParamByName('description').AsString := VariantToUTF8(vJSEvents.Value(intCount).Description)        else        ParamByName('description').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).Location) <> EmptyStr then        ParamByName('lieu').AsString := VariantToUTF8(vJSEvents.Value(intCount).Location)        else        ParamByName('lieu').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).Notes) <> EmptyStr then        ParamByName('commentaires').AsString := VariantToUTF8(vJSEvents.Value(intCount).Notes)        else          ParamByName('commentaires').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).Category) <> EmptyStr then        ParamByName('categorie').AsInteger := StrToInt(vJSEvents.Value(intCount).Category)        else          ParamByName('categorie').Clear;        //  TO DO!        if VariantToUTF8(vJSEvents.Value(intCount).AllDayEvent) <> EmptyStr then          ParamByName('alldayevent').AsBoolean := StrToBool(vJSEvents.Value(intCount).AllDayEvent)        else          ParamByName('alldayevent').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).DingPath) <> EmptyStr then        ParamByName('dingpath').AsString := VariantToUTF8(vJSEvents.Value(intCount).DingPath)        else        ParamByName('dingpath').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).AlarmSet) <> EmptyStr then        ParamByName('alarmset').AsBoolean := StrToBool(vJSEvents.Value(intCount).AlarmSet)        else          ParamByName('alarmset').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvance) <> EmptyStr then          ParamByName('alarmadvance').AsInteger := StrToInt(vJSEvents.Value(intCount).AlarmAdvance)        else          ParamByName('alarmadvance').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvanceType) <> EmptyStr then          ParamByName('alarmadvancetype').AsInteger := StrToInt(vJSEvents.Value(intCount).AlarmAdvanceType)        else        ParamByName('alarmadvancetype').Clear;        // SQLDateTimeToDateTime        if (Trim(VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime)) <> EmptyStr) and (vJSEvents.Value(intCount).SnoozeTime <> null) then        ParamByName('snoozetime').AsDateTime := SQLDateTimeToDateTime(vJSEvents.Value(intCount).SnoozeTime)        else          ParamByName('snoozetime').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).RepeatCode) <> EmptyStr then        ParamByName('repeatcode').AsInteger := StrToInt(vJSEvents.Value(intCount).RepeatCode)        else          ParamByName('repeatcode').Clear;        // SQLDateTimeToDateTime      if (Trim(VariantToUTF8(vJSEvents.Value(intCount).RepeatRangeEnd)) <> EmptyStr) and (vJSEvents.Value(intCount).RepeatRangeEnd <> null) then          ParamByName('repeatrangeend').AsDateTime := SQLDateTimeToDateTime(vJSEvents.Value(intCount).RepeatRangeEnd)      else          ParamByName('repeatrangeend').Clear;        //        if VariantToUTF8(vJSEvents.Value(intCount).CustomInterval) <> EmptyStr then          ParamByName('custominterval').AsInteger := StrToInt(vJSEvents.Value(intCount).CustomInterval)        else        ParamByName('custominterval').Clear;         // Update the database      ExecSQL;      end;    // for intCount := 0 to Pred(intRows) do  The SQLDateTimeToDateTime function I wrote is as defined below: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i function SQLDateTimeToDateTime(const SQLDateTime: string): TDateTime;begin  Result :=      EncodeDateTime(        SysUtils.StrToInt(Copy(SQLDateTime, 1, 4)),        SysUtils.StrToInt(Copy(SQLDateTime, 6, 2)),        SysUtils.StrToInt(Copy(SQLDateTime, 9, 2)),        SysUtils.StrToInt(Copy(SQLDateTime, 12, 2)),        SysUtils.StrToInt(Copy(SQLDateTime, 15, 2)),        0, 0      );end;  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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i  aServer.fDbProps.ExecuteNoResult(Format(         'INSERT INTO %s.tvp_event (' +            'tvp_resource_id, historique_emploi_id, ' +            'debut, ' +            'fin, ' +            'description, lieu, commentaires, categorie, alldayevent, dingpath, ' +            'alarmset, alarmadvance, alarmadvancetype, snoozetime, repeatcode, ' +            'repeatrangeend, custominterval) ' +          'VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ',               [Schema]), [VariantToUTF8(vJSEvents.Value(intCount).ResourceID),                VariantToUTF8(vJSIDs.contrat_id),                VariantToUTF8(vJSEvents.Value(intCount).StartTime),                VariantToUTF8(vJSEvents.Value(intCount).EndTime),                VariantToUTF8(vJSEvents.Value(intCount).Description),                VariantToUTF8(vJSEvents.Value(intCount).Location),                VariantToUTF8(vJSEvents.Value(intCount).Notes),                VariantToUTF8(vJSEvents.Value(intCount).Category),                VariantToUTF8(vJSEvents.Value(intCount).AllDayEvent),                VariantToUTF8(vJSEvents.Value(intCount).DingPath),                VariantToUTF8(vJSEvents.Value(intCount).AlarmSet),                VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvance),                VariantToUTF8(vJSEvents.Value(intCount).AlarmAdvanceType),              iif(VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime) = EmptyStr, null, VariantToUTF8(vJSEvents.Value(intCount).SnoozeTime)),              VariantToUTF8(vJSEvents.Value(intCount).RepeatCode),              VariantToUTF8(vJSEvents.Value(intCount).RepeatRangeEnd),              VariantToUTF8(vJSEvents.Value(intCount).CustomInterval);  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 · 2017-12-18 13:52 Any ideas anyone? ---------------------------------------------------------------------- tudi_x · 2017-12-18 14:53 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 · 2017-12-18 15:05 Quote from: tudi_x on December 18, 2017, 03:53:21 pmhi, 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 · 2017-12-18 15:12 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.