* * *

Author Topic: Sqlite and Time/Date what is the correct Format ?  (Read 588 times)

ThMSmann

  • Newbie
  • Posts: 4
Sqlite and Time/Date what is the correct Format ?
« on: October 06, 2017, 10:17:44 am »

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?

GetMem

  • Hero Member
  • *****
  • Posts: 2326
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #1 on: October 06, 2017, 10:32:27 am »
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

  • Newbie
  • Posts: 4
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #2 on: October 06, 2017, 11:10:26 am »
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.

GetMem

  • Hero Member
  • *****
  • Posts: 2326
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #3 on: October 06, 2017, 11:34:32 am »
@ThMSmann
Quote
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 !
Even if you save the date time as string? I don't believe you  :D. Now seriously you should use parameterised queries like this:
Code: Pascal  [Select]
  1.   SQLQuery1.SQL.Text := 'update MYTABLE set ZEIT = :pZeit where ID = 1';
  2.   SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY.MM.DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00));
  3.   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.

« Last Edit: October 06, 2017, 11:39:17 am by GetMem »

ThMSmann

  • Newbie
  • Posts: 4
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #4 on: October 06, 2017, 12:34:25 pm »

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 ?

GetMem

  • Hero Member
  • *****
  • Posts: 2326
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #5 on: October 06, 2017, 02:00:39 pm »
Quote
So 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]
  1. SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY.MM.DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00));
you need:
Code: Pascal  [Select]
  1. SQLQuery1.ParamByName('pZeit').AsString := FormatDateTime('YYYY-MM-DD hh:mm:ss.zzz', EncodeTime(12, 00, 00, 00));

ThMSmann

  • Newbie
  • Posts: 4
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #6 on: October 06, 2017, 03:11:32 pm »
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

  • Hero Member
  • *****
  • Posts: 4516
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #7 on: October 06, 2017, 03:47:06 pm »
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:
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...
« Last Edit: October 06, 2017, 04:01:23 pm by Thaddy »
"Logically, no number of positive outcomes at the level of experimental testing can confirm a scientific theory, but a single counterexample is logically decisive."

GetMem

  • Hero Member
  • *****
  • Posts: 2326
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #8 on: October 06, 2017, 04:04:21 pm »
Quote
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.
Thaddy you're not grumpy, that's a relief.  :D 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:
Quote
I have Projects in Delphi, FreePascal, C++ and C and such inconsistency are very time consuming to find out. :-(
I agree, this is annoying.
 
Quote
Thanks for your Help, and have a nice Day :-)
You're welcome. Did you consider to switch to another database engine.

Thaddy

  • Hero Member
  • *****
  • Posts: 4516
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #9 on: October 06, 2017, 04:25:57 pm »
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!! <grumpy  >:D >  (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  O:-))

Suggested music One of these days (Careful with that axe Eugene..) by Pink Floyd.
« Last Edit: October 06, 2017, 04:33:37 pm by Thaddy »
"Logically, no number of positive outcomes at the level of experimental testing can confirm a scientific theory, but a single counterexample is logically decisive."

GetMem

  • Hero Member
  • *****
  • Posts: 2326
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #10 on: October 06, 2017, 04:33:01 pm »
@Thaddy
Quote
<grumpy  >:D >
Now that's the spirit!  :D
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

  • Hero Member
  • *****
  • Posts: 4516
Re: Sqlite and Time/Date what is the correct Format ?
« Reply #11 on: October 06, 2017, 04:36:27 pm »
Now that's the spirit!  :D
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....
"Logically, no number of positive outcomes at the level of experimental testing can confirm a scientific theory, but a single counterexample is logically decisive."

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus