Jump to content
Chimp

Best way to store Dates in SQLite?

Recommended Posts

Chimp

Hi
I ask for generic advice on what field format is best to use to store dates in a database (SQLite).

if is a better choice to stored separately year, month and day into 3 separate fields or use one field for a complete date-time group?

I have to store information of this kind:
a person is assigned to a job for a certain day (one day only)
or also
a person is assigned to a job from a day to another day (a range of days)
then i have to query the database for a certain day so to know who is working to a certain job in a specific day.

Well, in the case of ranges of days (for example Mr. Bean works to JOB1 from May/15 to May/20) I have to store one record for each day of the range (6 record in this case) or use only one record with both dates in 2 fields of the same record?

the SQL query should 'ask' who is working on a specific day (even for days in the middle of the ranges).

My doubt is, what's the best way to store ranges of dates (as in the above example), so to be facilitated in the retrieval of those informations.

I'm afraid I'm not been clear, even if I did my best to be, anyway .... any suggestion is welcome..

Thank You

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
jchd

I'd use something simple but efficient enough for the purpose: ISO strings (YYYY-MM-DD ad time if necessary). Then create two columns DateFrom and DateTo. Then your query will be using a fast where clause:

select id, name, firstname from schedule where date('now') between datefrom and dateto;

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Chimp

Thank you @jchd!

exactly the answer I was looking for, concise and precise! :)

please, allow me one more question if I can,

What if I use a 'complete' date field as YYYY-MM-DD HH:MM:SS (date and time) but then I don't fill the time part?

That is, can I create the Date field (the column) with also the time part (just for possible future use) , but leave it empty? or I have to fill anyway, maybe with a 'fixed' time-stamp , say 12:00:00 for example? and if so, then the complete date-time must also be used in the SQL query or the time part can be optional ?

Thanks again

Edited by Chimp

small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
jchd

You can freely use full timestamps:

select date('now')    returns '2016-06-05'
select datetime('now')    returns '2016-06-05 19:00:09' (this is UTC time)

select '2016-06-19' between date('now') and '2017-12-31 23:59:59' as "workday"  returns 1 (true) in workday
select '2016-06-19 15:27:33' between date('now') and '2017-12-31' as "workday"  returns 1 (true) in workday

The magic with ISO dates is that they compare in all use cases, are human-friendly and are portable accross DBMSs, OSes, languages, countries.

  • Like 2

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Chimp

Wonderful!

Thanks a lot again.


small minds discuss people average minds discuss events great minds discuss ideas.... and use AutoIt....

Share this post


Link to post
Share on other sites
Skysnake

Thanks @jchd, I was not aware that you could select like that.  Will be simplifying my code. :)

@Chimp, you probably are aware, but SQLite supports this:

insert into test values (CURRENT_TIMESTAMP);

From here https://www.sqlite.org/lang_createtable.html

If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used in the new row is a text representation of the current UTC date and/or time. For CURRENT_TIME, the format of the value is "HH:MM:SS". For CURRENT_DATE, "YYYY-MM-DD". The format for CURRENT_TIMESTAMP is "YYYY-MM-DD HH:MM:SS".

 


Skysnake

Why is the snake in the sky?

Share this post


Link to post
Share on other sites
jchd

Additional note: you can define a more subtle default timestamp using date(), time(), datetime(), julianday() or strftime() with any modifier combination suits your needs.

CREATE TABLE "A" (
  "Id" INTEGER NOT NULL PRIMARY KEY, 
  "Item1" CHAR DEFAULT (datetime('now', 'localtime', '+5 minutes', '+12.3456 seconds')));

 

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • dangr82
      By dangr82
       
      I have created this function for a database, but I can not make it work. I always have two error messages: "not an error" ... and the file created, in the script directory, does not contain anything.
      Global $sDBName = "Hen.db" Func DatabaseTable() Local $sConnDB _SQLite_Startup() If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "SQLite.dll Can't be Loaded!") Exit -1 EndIf $sConnDB = _SQLite_Open($sDBName) If @error Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", "Can't Load Database!") Exit -1 EndIf If Not _SQLite_Exec($sDBName, 'CREATE TABLE Animal ("Name", "Age");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) If Not _SQLite_Exec($sDBName, 'INSERT INTO Animale VALUES ("Charlie","5");') = $SQLITE_OK Then MsgBox($MB_SYSTEMMODAL, "SQLite Error", _SQLite_ErrMsg()) _SQLite_Close($sConnDB) _SQLite_Shutdown() EndFunc  
    • Eminence
      By Eminence
      Hello,
      I've been searching on how to select the first row in my database which includes a time column based on the latest start_time date for today's date.
      This is my current code:
      Local $iCurrentDate = _Now() Local $sqlHandle = _dbOpen($sDbPath) Local $aResult, $iRows, $iCols _SQLite_GetTable2d($sqlHandle, "SELECT start_time FROM " & $srawDb & " ORDER BY datetime(start_time) DESC LIMIT 1", $aResult, $iRows, $iCols) _ArrayDisplay($aResult) _dbClose($sDbpath) What it does is it gets the first row entry in the database however it does not select the latest start_time entry in the database, it always select the first row. I've tried changing DESC to ASC to see if that will do it but the issue still remains. Any thoughts on this? Thanks in advance.
       
      *EDIT
      The date format in the database is by MM/DD/YYYY HH:MM:SS.
    • Seminko
      By Seminko
      In my recent project I'm downloading a bunch of data, so I decided to store it in a SQLite database. NOTE: I'm using sqlite3_x64.dll
      Everything is working just fine but I'm struggling with getting the Median value. SQLite has an Average function but not a Median one.
      I googled but all of the provided solutions are way above my pay-grade. After some more searching I found 'extension-functions.c' on the SQLite site where Median is included. After almost an hour of struggling I was able to successfully compile it into a DLL.
      So I downloaded @jchd's SQLiteExtLoad.au3 as seen here:
      But I'm getting these errors:
      "Path\SQLiteExtLoad.au3"(21,40) : warning: $g_hDll_SQLite: possibly used before declaration. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ "Path\SQLiteExtLoad.au3"(21,40) : error: $g_hDll_SQLite: undeclared global variable. Local $RetVal = DllCall($g_hDll_SQLite, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^ So I tried, copying the function to my file and changing the DLL variable ($g_hDll_SQLite) directly to the DLL location (C:\...\.. .dll), but now this error fires
      If __SQLite_hChk($hConn, 1) Then Return SetError(@error, 0, $SQLITE_MISUSE) To be honest, I don't know what to use as the $hConn - handle of connection.
      I would appreciate any help, be it getting the median using SQLite queries or getting the DLL extension loaded using AutoIt.
      Thanks, S.
       
      EDIT: well, I suspect the $hConn variable refers to the return value of the _SQLite_Open function. Well, at least now _SQLite_EnableExtensions doesn't give errors. Now I run into problems with _SQLite_LoadExtension, which gives error -1, and extended 1. Apparently the 1 constant is a generic error where other error do not apply.
      BTW, anybody knows whether I need to compile the extension DLL "into" x64 when I use a x64 SQLite? That might be the problem...
       
      EDIT2: I recompiled the dll and tried it using the SQLite3.exe and it works, so I'm confident the extension DLL has been created correctly

    • argumentum
      By argumentum
      ..let's say I store in a mysql column "[1,2,11,12,123]".
      How can I make s query to get all those rows that is 1, or is 12, etc ?
      I'm using  mysqlnd 5.0.11-dev - 20120503 
      Any advise on what technique I should use is welcomed
      Thanks
      PS: ..I'm adding a comma to each end, as in  [,1,2,11,12,123,] and will search with like %,12,% .
      Not pretty but I'm in a hurry and can't come up with a better solution =/
×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.