Sqlite and Time/Date what is the correct Format ? lazarus / 63-Database ---------------------------------------------------------------------- ThMSmann · 2017-10-06 08:17 Sqlite3, Admin Tools , Zeos, Delphi,... and even Lazarus/freepascal/TSQLDB when you use direct SQL Statment save Date and Time as    Real numbers :  0="1899-12-30 00:00:00" Lazarus,Freepascal,TSQLDB when u use TSQLDB's Components saves time as   Real numbers :   0="January 1, 4713 BC 12:00:00" Julian Date   So every Tool is different to TSQLDB ? Or am i missing something? ---------------------------------------------------------------------- balazsszekely · 2017-10-06 08:32 Hi ThMSmann, Sqlite stores Datetime as Text or Real or Integer. See here: http://www.sqlite.org/datatype3.html#datetime You should do the same from lazarus/fpc: 1. Store as string. Make sure the string format mach the one in the documentation. 2. Convert TDateTime to Julian date and vice versa. You can find the appropriate functions in dateutils unit, DateTimeToJulianDate and JulianDateToDateTime respectively 3. Store it as Unix Time integer ---------------------------------------------------------------------- ThMSmann · 2017-10-06 09:10 Hello GetMem, Yes this all works, that's no Problem if u use only TSQLDB. But if u use for example an SQL Admin Tool you cant read the Time or Dates ! And if u use "Update DB  SET Zeit='12:00:00' where ID=1" TSQLDB saves the Time in Excel/Pascal Format, this is what i use as workaround, to have a consistent Time Format in the Databases. Despite what they write in /www.sqlite.org/datatype3.html#datetime , maybe i should ask in the sqlite3 forums whats going on!? and what is the correct way to handle this divergence. ---------------------------------------------------------------------- balazsszekely · 2017-10-06 09:34 @ThMSmann QuoteYes this all works, that's no Problem if u use only TSQLDB. But if u use for example an SQL Admin Tool you cant read the Time or Dates !Even if you save the date time as string? I don't believe you  . Now seriously you should use parameterised queries like this: 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   SQLQuery1.SQL.Text := 'update MYTABLE set ZEIT = :pZeit where ID = 1';  SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY.MM.DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00));  SQLQuery1.ExecSQL;The above example is the exact reason why parameterised queries was invented in the first place. Please test it and let me know if it works. ---------------------------------------------------------------------- ThMSmann · 2017-10-06 10:34 Hello GetMem, first thanks for your reply's :-) The Sql is the same as i wrote as example only, but parametrized which i will use in  my todo functions. I looked in the db with a hex Editor and now I know what happens! All tools etc use the Text Format. (not the old real format as i suggested first) but TSQLDB uses the Real Julian Format. SQLQuery1.fieldbyname('Zeit').value := '12:00:00';  <- Result Julian Date in DB SQLQuery1.fieldbyname('Zeit').asDateTime := TDateTimeSomething;  <- Result Julian Date in DB So the Default Format is different. Is there a way to set the default Format in TSQLDB ? ---------------------------------------------------------------------- balazsszekely · 2017-10-06 12:00 QuoteSo the Default Format is different. Is there a way to set the default Format in TSQLDB ?AFAIK there is no default format, but if everyone else use the Text format, you should also use it. In my previous example I made a small mistake, instead of: 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 SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY.MM.DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00));you need: 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 SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY-MM-DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00)); ---------------------------------------------------------------------- ThMSmann · 2017-10-06 13:11 Hi GetMem, Yes, that's what i will do. But i would have been nice that all functions in a Library use the same Data Format. I have Projects in Delphi, FreePascal, C++ and C  and such inconsistency are very time consuming to find out. :-( Thanks for your Help, and have a nice Day :-) ---------------------------------------------------------------------- Thaddy · 2017-10-06 13:47 Quote from: GetMem on October 06, 2017, 02:00:39 pmAFAIK there is no default format, but if everyone else use the Text format, you should also use it. In my previous example I made a small mistake, instead of: AFAIK there IS a default format. The default format (both in float as in integer) on Database level should always be yyyy/mm/dd since that corresponds to both possible binary representations and can easily compute. This has always been the case for as long as I have been involved in computing (1978) and probably long before that, because it is logic and logical. How would one describe a date (in computing terms) otherwise? (I know that happens, but that's for morons. You are not one of them) @ThMSmann: There has never been an inconsistency in the theory, there have been many cases where a programmer invented wheels that are not round... ALL SQL versions, dBase, Paradox, well, basically any database software that is well designed sticks to the above. If you meet anything else, blame the programmer.... The format is consistent between programming languages too..... Including COBOL and FORTRAN... ---------------------------------------------------------------------- balazsszekely · 2017-10-06 14:04 QuoteAFAIK there IS a default format. The default format (both in float as in integer) on Database level should always be yyyy/mm/dd since that corresponds to both possible binary representations and can easily compute.Thaddy you're not grumpy, that's a relief.  If I understood @ThMsmann correctly, he was looking for a setting in TSQLQuery where he can specify how the database engine should save the date time: as integer, as string or real. It's not the same thing. He's worried about the inconsistency between different tools(read above) and I agree with him. @ThMSmann:QuoteI have Projects in Delphi, FreePascal, C++ and C and such inconsistency are very time consuming to find out. :-(I agree, this is annoying. QuoteThanks for your Help, and have a nice Day :-)You're welcome. Did you consider to switch to another database engine. ---------------------------------------------------------------------- Thaddy · 2017-10-06 14:25 In this case it is not the database engine but the lack of in-depth knowledge of the one that caused his problems in the first place: an under-educated programmer!!   (not OP!) But anyway, to understand the freedom of a particular database format you need to know something. Sqlite is a SIMPLE format, but, given educated programmers, it WILL store date and time correctly. Blame the programmer.... (Or explain that simple things can be dangerous in the hands of simple minds  ) Suggested music One of these days (Careful with that axe Eugene..) by Pink Floyd. ---------------------------------------------------------------------- balazsszekely · 2017-10-06 14:33 @Thaddy Quote Now that's the spirit!  OP is obviously not under-educated. As you can see, he writes program in c, c++, pascal etc..Just not familiar with the quirks of database programming in lazarus/freepascal. ---------------------------------------------------------------------- Thaddy · 2017-10-06 14:36 Quote from: GetMem on October 06, 2017, 04:33:01 pmNow that's the spirit!  OP is obviously not under-educated. As you can see, he writes program in c, c++, pascal etc..Just not familiar with the quirks of database programming in lazarus/freepascal. Oh well... (<--- it's Friday and by Fleetwood Mac) Those quirks should not be there: database language theory is programming language neutral... Always has been... Have a nice weekend....