Thank you for all the responses to my original post. They were all very helpful in formulating my position/solution: Unless you deal with financial services, Facebook, Twitter type systems processing multi-million / billions of records or high volumes / TPS that would be hampered by text/string conversion and extraordinary date calculations, I think storing the timestamp in a string format in UTC time, using the ISO 8601 notation is the right answer for a portable, flexible, usable timestamps e.g., YYYY-MM-DDThh:mm:ssZ.
I had some fun reading your responses and digging into some of the details. Only if you're interested, here is my rationale for recommending text/string format using ISO representation over using an integer in Unix time. SQLite and most RDBMS can handle efficiently storing and converting between ISO 8601 string and integer Unix/POSIX/Epoch timestamp (date/time) representations. Converting back and forth, for me, isn’t really a deciding factor. The biggest consideration factor for me ended up being storage. Unix time wins over a string representation hands down from a storage perspective, 2 to 3 times less storage. In order to get around the "2038 bug" for Unix time, you'll need to use an unsigned 4 byte integer (or larger). At 3:14:07UTC Tuesday 19 January, there will have been 2,147,483,647 or 2^32-1 seconds that past since 1 January 1970 – one second later and you would roll over to a negative number (I think). In order to represent the date/time in UTC in a text/string format, you'd need at a minimum of 15 bytes (YYYYMMDDhhmmssZ) and a full 20 bytes using the full notation (YYYY-MM-DDThh:mm:ssZ). Since SQLite doesn't support unsigned integers and based on the feedback from Baruch Burstein, I assume SQLite would store the integer as a 4 byte integer until it rolled past 2^32-1 and then would expand it to a 6 byte integer. Nonetheless, from a storage perspective it is a 4-6 byte integer vs. a 15-20 byte string. That seems like a lot per record, but it really isn't for most uses. You would need to store almost 75,000 timestamps to equate to 1MB, 1 million records for 13MB, and you'd have store over 76 million timestamps to get to 1GB. That said, if you were storing a billion records, the text/string format would cost you an extra 931GB. This leaves me in thinking for the general user, storage, even on mobile devices, probably even in memory databases, storing the timestamp in a text/string representation won't really be that big of a deal. I am sure performance comes into play at high volumes/TPS, particularly the cost to convert strings to integers for calculations and then back to strings again. To do a thorough evaluation, I would want to dig into specific implementations and do my own tests. I just don’t have the time and don’t think it will be a factor at my relatively low volumes. If any of you have dug into the performance aspects, I’d be interested in hearing. Thanks again, Chris On Wed, Jan 14, 2015 at 9:57 AM, Doug Nebeker <ad...@poweradmin.com> wrote: > Whatever format you choose to store it in, I highly recommend storing the > UTC time. It might be a little more work, but: > > 1. your program can display the correct local time, even if the > database/app/user is in/changes to another timezone > 2. you won't have to deal with seeing two 1:30am on the day that day light > savings kicks in > > > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Chris Keilitz > Sent: Wednesday, January 14, 2015 7:09 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] Best Practice: Storing Dates > > I'm a new sqlite user and new to this mailing list. I hope this question > is appropriate. > > I am writing an application that needs to track a timestamp - date + time > down to the seconds. I'd like to store the date/time in a standard, > efficient, usable, portable format. I have looked over the sqlite > date/time functions / data types and the ISO 8601 standard and have landed > on these two options: > > 1. Storing it in TEXT format e.g., "YY-MM-DD HH:MM:SS" or 2. Storing it as > an INTEGER (LONG) in Unix Time (AKA: POSIX or Epoch > time) - number of seconds since 01/01/1970 > > Since sqlite and most RDMS implementations have functions to convert to > and from both options and using a LONG should allow the date/time to > function way past 2038, it seems it comes down to how many bytes it takes > to store the timestamp and how fast are the conversion routines. The > application I'm writing won't push any performance boundaries and likely > won't need to overly worry about storage. I just want to make the right > call on data type and format and learn something in the process. > > Again, I hope this is an appropriate post for this mailing list. If not, I > apologize. > > Thanks! > > Chris > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
