# DateTime to SQLite **URL:** \ **Category:** Databases\ **Created:** [March 3, 2018, 7:51am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307 "2018-03-03T07:51:23Z")\ **Posts on this page:** 9\ **Page:** 2
**Author:** ![Simon\_Berridge](https://forum.xojo.com/letter_avatar_proxy/v4/letter/s/cc9497/32.png) [@Simon\_Berridge](https://forum.xojo.com/u/Simon_Berridge)\ **Post date:** [August 22, 2018, 10:45am UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/21 "2018-08-22T10:45:26Z")
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 ```auto Select * from MyTable where MyDate between '2018-01-01' and '2018-07-31' ``` +1 for Dave’s reply. ---
**Author:** ![Edwin\_van\_den\_Akker](https://forum.xojo.com/user_avatar/forum.xojo.com/edwin_van_den_akker/32/1063_2.png) [@Edwin\_van\_den\_Akker](https://forum.xojo.com/u/Edwin_van_den_Akker)\ **Post date:** [August 22, 2018, 3:41pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/22 "2018-08-22T15:41:42Z")
> [@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? ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [August 22, 2018, 3:46pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/23 "2018-08-22T15:46:03Z")
> [@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 ---
**Author:** ![Edwin\_van\_den\_Akker](https://forum.xojo.com/user_avatar/forum.xojo.com/edwin_van_den_akker/32/1063_2.png) [@Edwin\_van\_den\_Akker](https://forum.xojo.com/u/Edwin_van_den_Akker)\ **Post date:** [August 22, 2018, 3:51pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/24 "2018-08-22T15:51:25Z")
[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. ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [August 22, 2018, 4:07pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/25 "2018-08-22T16:07:17Z")
> [@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. ---
**Author:** ![Edwin\_van\_den\_Akker](https://forum.xojo.com/user_avatar/forum.xojo.com/edwin_van_den_akker/32/1063_2.png) [@Edwin\_van\_den\_Akker](https://forum.xojo.com/u/Edwin_van_den_Akker)\ **Post date:** [August 22, 2018, 4:08pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/26 "2018-08-22T16:08:55Z")
[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\>\\<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><2018-03-02 21:24:00>"[/quote] ``` ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [August 22, 2018, 6:13pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/27 "2018-08-22T18:13:00Z")
> [@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? ---
**Author:** ![DaveS](https://forum.xojo.com/letter_avatar_proxy/v4/letter/d/848f3c/32.png) [@DaveS](https://forum.xojo.com/u/DaveS)\ **Post date:** [August 22, 2018, 6:52pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/28 "2018-08-22T18:52:37Z")
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. ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 7:07pm UTC](https://forum.xojo.com/t/datetime-to-sqlite/42307/29 "2020-10-29T19:07:14Z")
[Previous page](https://forum.xojo.com/t/datetime-to-sqlite/42307.md?page=1)