Hi,
francof wrote:about date format, do you tell me why "-" as the separator instead of "/" ?
SQLite likes it this way ;-) Check
the doku.
I recently worked with SQLite & datetimes, and found it a lot of hassle. Especially since I'm in Europe, and LC's date & time functions are mostly anglo-centric. I ended in using the dateItems, and in converting forth and back.
To get the dateItems from SQLite:
Code: Select all
"SELECT strftime('%Y,%m,%d,%H,%M,%S,%w',Table.Field) FROM Table"
Hint: don't rely on the last item, numeric day - different in EU/Anglo too!
For the conversions I used this:
Code: Select all
function DI2Sys DI
-- DateItems -> 23.03.2016 12:44
set the itemdelimiter to ","
return item 3 of DI & "." & item 2 of DI & "." & item 1 of DI & " " & item 4 of DI & ":" & item 4 of DI
end DI2Sys
function Sys2DI MyTime
-- 23.03.2016 12:44 -> DateItems
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "," & item 2 of TheDate & "," & item 1 of TheDate & "," into MyVar
set the itemdelimiter to ":"
put item 1 of TheTime & "," & item 2 of TheTime & ",00,0" after MyVar
return MyVar
end Sys2DI
function Sys2SQL MyTime
-- 23.03.2016 12:44 -> 2016-03-23 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "-" & item 2 of TheDate & "-" & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end Sys2SQL
function SQL2SYS MyTime
-- 2016-03-23 12:44:00 -> 23.03.2016 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "-"
put item 3 of TheDate & "." & item 2 of TheDate & "." & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end SQL2Sys
So I do this in my stack:
- I show the system date/time (european, SYS)
- I calculate with the dateItems (DI)
- And when writing to SQLite, I use a format it likes (SQL) :)
I'm sure there's a more elegant way, though ...
Have fun!