Which data type is better for date?

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Which data type is better for date?

Mike Zang-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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: [hidden email]
> To: [hidden email]
> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
     
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Ben-273
In reply to this post by Mike Zang-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Zanardo
In reply to this post by Mike Zang-2
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 <[hidden email]> 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
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Mike Zang-2
In reply to this post by Kristoffer Danielsson
> #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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Theodore M. Rolle, Jr. (Ted)
In reply to this post by Kristoffer Danielsson
-----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: [hidden email]
>> To: [hidden email]
>> 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
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>      
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> 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 78164    of 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Mike Zang-2
In reply to this post by Ben-273
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 <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Mike Zang-2
In reply to this post by Zanardo
I only need date, no time is ok, do you have more less memory method?

--- Zanardo <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Mike Zang-2
In reply to this post by Theodore M. Rolle, Jr. (Ted)
It is ok even if use local time, because using UTC will let thing getting
complex.

--- "Ted Rolle Jr." <[hidden email]> 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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Gabor Grothendieck
In reply to this post by Mike Zang-2
On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang <[hidden email]> 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 yyyy-mm-dd representations and also handle
other manipulations in those formats so you probably want to choose
one of those.  yyyy-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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Which data type is better for date?

Alan Chandler
In reply to this post by Mike Zang-2
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
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users