Re: [sqlite] Which data type is better for date?

[Alan Chandler]

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?

[Gabor Grothendieck]

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?

[Mike Zang]

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?

[Mike Zang]

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?

[Mike Zang]

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:];
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?

[Ted Rolle Jr.]

-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?

[Mike Zang]

> #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?

[Zanardo]

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?

[Ben]

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?

[Kristoffer Danielsson]

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