DateTime to SQLite

[Simon_Berridge]

The problem with TotalSeconds is that fit is Xojo based.

I use two development environments - Xojo for my GUI and most database applications and a different environment for my dylib/dll development. The second environment starts its date second counter at 1 January 1970. This means that when I read the database data entry in the second environment I have to convert the total seconds - both in and out.

Dave’s answer is the correct one. SQLDateTime saves the date as ‘YYYY-MM-DD HH:MM:SS’ and all the databases that I have used and currently use read these dates correctly. They work on database sorting and in all Select statements like

Select * from MyTable where MyDate between '2018-01-01' and '2018-07-31'

+1 for Dave’s reply.

[Edwin_van_den_Akker]

@375881:@Greg O'Lone

You could just store seconds from epoch and restore it from that. Don’t forget to store the time zone if you’re not using GMT!

Always convert time to GMT (or UTC) before storing. That makes syncing and cross-timezone use way easier. Also, remember that DST can mess things up. That’s why I always store my dates as UTC time, and yes, I use Epoch (UNIX) timestamps.

Once displayed, convert to the user’s local timezone.

@375893:@Alain Clausen

I often use a field with TotalSeconds as REAL and another one as TEXT only used for search.

The idea of storing a date as a TEXT field is interesting. But isn’t it better to convert the search string to the “Total Seconds” value, and do the search based on that converted value?

[DaveS]

@402018:@Edwin van den Akker

The idea of storing a date as a TEXT field is interesting. But isn’t it better to convert the search string to the “Total Seconds” value, and do the search based on that converted value?

uh… no… again… “total seconds” could vary between systems for one.
two it most likely will lock you database to a Xojo environment
and as far as SQLite is concerned… data is data… there are no datatypes…

but feel free to use what ever method you think works best

[Edwin_van_den_Akker]

[quote=402019:@Dave S]uh… no… again… “total seconds” could vary between systems for one.
two it most likely will lock you database to a Xojo environment
and as far as SQLite is concerned… data is data… there are no datatypes…

but feel free to use what ever method you think works best[/quote]

As I said in my previous message, I store in UNIX time. So, I almost never use the Xojo-TotalSeconds standard.
And of course, SQLite doesn’t have datatypes. But I understand the idea of storing the datetime in several formats as a textstring, for speeding up the search. I think converting the search string to the UNIX-timestamp and perform a SELECT from that seems more efficient.

[DaveS]

@402021:@Edwin van den Akker

several formats as a textstring

not several… just ONE… SQLDATE format is an industry standard, and is support (and recommended) by almost every database vendor in the world.

[Edwin_van_den_Akker]

[quote=402023:@Dave S]build a fast search engine
allow the user to find the data without having to know the format recorded in the db
example for the search field: “<02/03/2018 21:24><March 2, 2018 21:24><2018-03-02 21:24:00>”[/quote]

I know Dave. I was just revering to:

[quote=375893:@Alain Clausen]The second field allows you to save the date in different formats and therefore:

build a fast search engine
allow the user to find the data without having to know the format recorded in the db
example for the search field: "<02/03/2018 21:24><March 2, 2018 21:24><2018-03-02 21:24:00>"[/quote]

[TimStreater]

@402019:@Dave S

uh… no… again… “total seconds” could vary between systems for one.

I don’t see how secondsFrom1970 can vary between platforms.

two it most likely will lock your database to a Xojo environment

Since “seconds since the epoch” is a double, how can that lock me to a Xojo environment?

[DaveS]

this conversation is going no where… I made my argument for what I consider the “right way” and stated that any method (and the consequences) were the free choice of the developer.