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
