Re: [sqlite] Which data type is better for date?
On 04/09/10 12:31, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > I don't think this applies to you, but I had to build an application where time for the user has to be reasonably accurate (an American Football picking competition, where the deadline was 5 minutes before each match) My users are worldwide. I realised that on the server end, I could carry the date/time around as a UNIX timestamp (ie seconds from 1970 UTC) and then use javascript on the client end (in a browser) to locally display stuff as (after multiplying by 1000). As a result, I almost always think about that approach as my first choice when writing a new app. -- Alan Chandler http://www.chandlerfamily.org.uk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1. integer as seconds since 1970 > 2. integer as days since 1970 > 3. string as '2010-09-03' > 4. string as '10-09-03' sqlite has julianday and date sql functions which convert back and forth between julianday (number of days since noon in Greenwich on November 24, 4714 B.C.) and -mm-dd representations and also handle other manipulations in those formats so you probably want to choose one of those. -mm-dd does have the advantage that its easier to look at the raw data in the database. Also, if you are only dealing with dates and do not need to consider time zones then its best to use a representation that uses neither times nor time zones since those can introduce errors which are artifacts of the representation. time zone errors (confusion between UTC and current time zone) can be particularly subtle. sqlite> select date("now"); 2010-09-04 sqlite> select date("2000-01-01", "+1 day"); 2000-01-02 sqlite> select julianday(date("now")) - julianday("2010-09-01"); 3.0 sqlite> select date(julianday(date("now"))); 2010-09-04 See: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
It is ok even if use local time, because using UTC will let thing getting complex. --- "Ted Rolle Jr." wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > And in addition, the TZ offset might be handy to convert to UTC. > Local > time is locally determined while UTC is constant, and other local > offsets can be applied to display time in local terms. > For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT is UTC-7; > During WWII there was a ``War Time''. Some countries have a > half-hour > offset in addition to the hour offset, so 2010-09-03T09:10:12+4:30 is > a > valid time. It's _all_ politics; this makes it subject to the whim > of > each government. So, in addition to the half-hour offsets, time-zone > offsets may change. Also, the determination of Daylight Savings time > varies by country and can correspondingly change. UTC is best. > That's > the reason Unix uses seconds since 1970. I don't know what they do > for > dates before that; if the time can have a negative offset (proleptic) > then all is well. > > Hmmm...Ask me the time; I'll give you my watch. :-) > > Ted ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
I only need date, no time is ok, do you have more less memory method? --- Zanardo wrote: > While timestamps (seconds since 1970) need less storage space, I tend > to store dates and times with this format: > > 2010-09-04 09:15:37 > > This is more readable for ad-hoc queries, and you can easily use > range > operations with a simple BETWEEN or a "<=" and ">=". SQLite has a > built-in function to generate this timestamp with the current date > and > time within the current time zone: > > SELECT datetime('now', 'localtime') ; > > Zanardo. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
Ben In fact, I am converting a binary file to SQLite3, the file is in format as below: struct Stock { int day; int open; int high; int low; int close; double volume; }; and I use code as below to get NSDate with 2010-09-03 00:00:00 #define kSecondsRest 18 * 60 * 60 - 59 * 60 - 28 NSData *data = [NSData dataWithContentsOfFile:file options:0 error:&error]; struct Stock *stock = (struct Stock*)[data bytes]; int seconds = 86400 * (stock->day + 125913) - kSecondsRest; int hours = seconds / 3600; NSDate *date = [NSDate dateWithTimeIntervalSince1970:seconds]; --- Ben wrote: > Mike, > > If you are using iOS, then presumably you are using the NSDate class. > If you are, then the easiest thing to do is store the result of > - (NSTimeInterval)timeIntervalSinceReferenceDate . This stored value > can be turned back into an NSDate using [NSDate > dateWithTimeIntervalSinceReferenceDate:] > > The type of NSTimeInterval is a double. This can be stored easily and > has good precision (see > http://developer.apple.com/mac/library/documentation/Cocoa/Reference/Foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html#//apple_ref/c/tdef/NSTimeInterval > ) > > Any further discussion along these lines would probably be better > taken to a mac development list such as cocoa-dev. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 And in addition, the TZ offset might be handy to convert to UTC. Local time is locally determined while UTC is constant, and other local offsets can be applied to display time in local terms. For example, EST is UTC-5; EDT is UTC-4; PST is UTC-8; PDT is UTC-7; During WWII there was a ``War Time''. Some countries have a half-hour offset in addition to the hour offset, so 2010-09-03T09:10:12+4:30 is a valid time. It's _all_ politics; this makes it subject to the whim of each government. So, in addition to the half-hour offsets, time-zone offsets may change. Also, the determination of Daylight Savings time varies by country and can correspondingly change. UTC is best. That's the reason Unix uses seconds since 1970. I don't know what they do for dates before that; if the time can have a negative offset (proleptic) then all is well. Hmmm...Ask me the time; I'll give you my watch. :-) Ted On 09/04/2010 08:00 AM, Kristoffer Danielsson wrote: > > Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time > of the day, which may prove useful later on. > > > > #2 is non-standard. No time value. > > #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option > though. > > #4 gives room for mistakes; the year may be interpreted as the day. > > >> Date: Sat, 4 Sep 2010 20:31:00 +0900 >> From: mikez...@yahoo.co.jp >> To: sqlite-users@sqlite.org >> Subject: [sqlite] Which data type is better for date? >> >> I try to convert data to SQLite3 for iPad, please give me some detail >> suggestion. >> >> I think that I can save date value as below to SQLite3, I want to know >> which is better, or anything else if you have good idea. >> >> 1. integer as seconds since 1970 >> 2. integer as days since 1970 >> 3. string as '2010-09-03' >> 4. string as '10-09-03' >> ___ >> 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 - -- +-+ | 3.14159 26535 89793 23846 26433 83279 50288 May the Spirit| | 41971 69399 37510 58209 74944 59230 78164of pi spread | | 06286 20899 86280 34825 32411 70679 82148 around the world. | | 08651 32823 06647 09384 46095 50582 ... PI VOBISCUM! | +-+ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQIcBAEBAgAGBQJMgkaKAAoJED4OSd2wf5qud7kQAIeQXUY/A+AQgj5lduv8v2fu +nbpSe5YxlfLSG8BdQiMGBcvDSvqXwzIFuW7epPO9LO7uDQFpkEAUnOoQBVdtP53 NrsYt9mXtniXe5y8o7wI4pvZv9kW4r9vL4+ahwWYROT/UaCJOwPGgvpf9/S8zbp+ VMpO71I7ZImgMh70976EAvJUx3e/4Eha5S/vVJiG/REFnG6zibI6dssEhDQBlBsW ePBEQE1Rif7eJB5NVEfpIKauBeI0uWL/FW1+omwcTGPM6c1WnRIdz5gKt2VAgNV0 C1y0MO/82qAt1EQEgYtm1ft8nVUoAwIg8sdVPZlrVHqQq++x065NdkipcqbhsTA5 /lBj0rAKhntNDE6BYbxEhYs+3LAgi+d42+Sq/kY4JW65eiaffzzKHu+/LSvg1Vj8 291pG18RfsSxy7jqyplOpDBkybaITgmyY7Lhi/QBy8YDccqiWPWAVYf5Kjfe96X2 wW93RiGe5efRHUI2H2TLoPwy73O3rkzV9Q35oPhx0TFygRuxCDKZTSWvg4vROpHD NBJFLoMhtge1tTy1VCiEiPSUEX9BrxaEuaxjDhm2rpvP55zDQXLEcMtEIt6ur21w EZ+3xrUMZkwc5OjzD6US1It+c7mFUfz2SKFZQPNo8Jvo5gMPxhJ3PpjD6ySTasnC k+kTlA4gbe8s/CuciObC =8GoS -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
> #2 is non-standard. No time value. when I select, I will use days * 3600 > #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an > option though. I will convert it to Date when select > #4 gives room for mistakes; the year may be interpreted as the day. maybe you are right. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
While timestamps (seconds since 1970) need less storage space, I tend to store dates and times with this format: 2010-09-04 09:15:37 This is more readable for ad-hoc queries, and you can easily use range operations with a simple BETWEEN or a "<=" and ">=". SQLite has a built-in function to generate this timestamp with the current date and time within the current time zone: SELECT datetime('now', 'localtime') ; Zanardo. On Sat, Sep 4, 2010 at 8:31 AM, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1. integer as seconds since 1970 > 2. integer as days since 1970 > 3. string as '2010-09-03' > 4. string as '10-09-03' > ___ > 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
Re: [sqlite] Which data type is better for date?
Mike, If you are using iOS, then presumably you are using the NSDate class. If you are, then the easiest thing to do is store the result of - (NSTimeInterval)timeIntervalSinceReferenceDate . This stored value can be turned back into an NSDate using [NSDate dateWithTimeIntervalSinceReferenceDate:] The type of NSTimeInterval is a double. This can be stored easily and has good precision (see http://developer.apple.com/mac/library/documentation/Cocoa/Reference/Foundation/Miscellaneous/Foundation_DataTypes/Reference/reference.html#//apple_ref/c/tdef/NSTimeInterval ) Any further discussion along these lines would probably be better taken to a mac development list such as cocoa-dev. On 4 Sep 2010, at 12:31, Mike Zang wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1. integer as seconds since 1970 > 2. integer as days since 1970 > 3. string as '2010-09-03' > 4. string as '10-09-03' > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Which data type is better for date?
Definitely #1 if iOS supports 64-bit unix timestamps. You also store the time of the day, which may prove useful later on. #2 is non-standard. No time value. #3 has no time value. '2010-09-03T01:23:45' (ISO 8601) would be an option though. #4 gives room for mistakes; the year may be interpreted as the day. > Date: Sat, 4 Sep 2010 20:31:00 +0900 > From: mikez...@yahoo.co.jp > To: sqlite-users@sqlite.org > Subject: [sqlite] Which data type is better for date? > > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1. integer as seconds since 1970 > 2. integer as days since 1970 > 3. string as '2010-09-03' > 4. string as '10-09-03' > ___ > 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