Re: [sqlite] DateTime Objects

[Kees Nuyt]

On Sat, 28 Feb 2009 17:30:24 -0800 (PST), jonwood
 wrote:

>Thanks, but I'm not sure what this means. "SQLite date storage format and
>support" doesn't appear to be a specific term (at least, it didn't turn up
>anything specific on Google). 

I'm almost sure John Stanton had this in mind:

The number of days since noon in Greenwich on November 24,
4714 B.C. , as described in the return value of julianday().

http://www.sqlite.org/lang_datefunc.html
http://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar

Then, use the SQLite datetime functions to return any of a
few supported formats your application can cope with.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[John Stanton]

Look at the Sqlite sourcce code in the date function area and all, is 
revealed.

jonwood wrote:
> John Stanton-3 wrote:
>   
>> Use the Sqlite date storage format and support.  With that approach 
>> which is astronomivally correct you can deliver any date format or 
>> manipulwtion,  You may need some custom written functions.  to get week 
>> number according to national rules etc, but the method is sound.  It is 
>> also compatible with different date systems.
>>
>> 
>
> Thanks, but I'm not sure what this means. "SQLite date storage format and
> support" doesn't appear to be a specific term (at least, it didn't turn up
> anything specific on Google). Is there a link that would describe what
> you're referring to and how it'd help my situation?
>
> Thanks.
>
> Jonathan
>
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

>Storing dates with a two-digit year is...  The deja vu, the deja vu!
>Why, oh why re-create y2k?!

No one's talking about storing them that way. As far as printouts, I'm not
overly concerned about this client's reports being ambiguous in the year
2100.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22269622.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Jay A. Kreibich]

On Sat, Feb 28, 2009 at 12:28:33PM -0800, jonwood scratched on the wall:
> 
> 
> Derrell Lipman wrote:
> > 
> > http://sqlite.org/lang_datefunc.html
> > 
> 
> Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
> leading zeros, etc. Just as I pointed out in my original post.

  It should be pretty trivial to add those, however.  The strftime()
  function is right there in the code.  You could add a 'y' for
  two-digit years with about six lines of code.  Similar for 'h' - 12
  hour value, or 'A' - AM/PM indicator, or whatever.

  If you're not comfortable modifying the SQLite source for
  distribution reasons, or something like that, just bulk copy
  the strftime() function to your own external module or something and
  add/rewrite the bits you want.  Then you can link it in to any
  instance of SQLite you want.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Nicolas Williams]

On Sat, Feb 28, 2009 at 12:47:07PM -0800, jonwood wrote:
> > Database is for manipulating data. Your UI application is for presenting 
> > it nicely to the user. After all, you don't complain that SQLite, say, 
> > doesn't have functions for formatting numbers in user-friendly manner 
> > (e.g. 123,456.78).
> 
> So why does it have to be pre-formatted by storing it as text that I must
> parse and then reformat? If it just stored a date object in binary
> format--as a database should do--then I could easily format it to present to
> the user.

Why do you have to notice that SQLite3's date type is actually TEXT?
Use the functions provided and don't think of how they work (text
parsing and all that).  Just because a type isn't opaque doesn't mean
that you can't treat it as such...

> > You can store dates as doubles representing Julian dates, or as integers 
> > representing number of seconds since Unix epoch (aka time_t). Is this 
> > the kind of bindary format you are talking about?
> 
> Well, I don't know many CRT routines for working with Julian dates. time_t
> has support but they've kind of moved to a 64-bit version. I guess I could
> store it as a BLOB or store the year, month, day, hour, minute, and second
> in separate fields as well. But that doesn't seem like a very good approach
> to me.

No, no, just use SQLite3's date/time functions to convert to whatever
presentation form you want.  You can use them on the text form of dates
and on the integer form both.

Don't bother writing your own code to do these conversions when they are
already provided for you!

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Nicolas Williams]

On Sat, Feb 28, 2009 at 06:14:05PM -0800, jonwood wrote:
> >So I would just rock it and not worry about it too much. If you're really
> >that hard up on saving CPU cycles, they might be better gained elsewhere.
> 
> I just like to be efficient and thought I'd check in to see if I was missing
> anything. Looks like I'm not.

Storing dates with a two-digit year is...  The deja vu, the deja vu!
Why, oh why re-create y2k?!

Premature optimization and all that.

If INSERTs are rare and you're CPU-bound for SELECTs (because your DB
fits in memory) then just INSERT raw time and pre-computed presentation
forms.  If you're not CPU-bound then don't worry.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

>You may already be aware of this, but I didn't see it here in the
discussion
>so I thought I'd chime in. SQLite doesn't really provide a native datetime
>type. The data type documentation lays out what's going on in detail (link
>below), but I'm pretty certain that your datetime column is actually a TEXT
>type.

Yes, I did know there was something like that going on. And perhaps that's
the crux of the issue I'm having.

>So I would just rock it and not worry about it too much. If you're really
>that hard up on saving CPU cycles, they might be better gained elsewhere.

I just like to be efficient and thought I'd check in to see if I was missing
anything. Looks like I'm not.

Thanks,

Jonathan

-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22269245.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Billy Gray]

Hi Jonathon,
You may already be aware of this, but I didn't see it here in the discussion
so I thought I'd chime in. SQLite doesn't really provide a native datetime
type. The data type documentation lays out what's going on in detail (link
below), but I'm pretty certain that your datetime column is actually a TEXT
type.

http://sqlite.org/datatype3.html

If this poses an efficiency problem, others have suggested some good
work-arounds earlier. I suspect that for the general use-cases of SQLite,
storing date-time information in this way isn't really going have too bad of
an impact. And, as others have pointed out, you'll almost always have to do
some kind of mapping in your application from how the db stores date-times
to how your platform likes to do it (i.e. NSDate), and then you'll be
manipulating those objects at presentation time for the end-user.

So I would just rock it and not worry about it too much. If you're really
that hard up on saving CPU cycles, they might be better gained elsewhere.

Cheers,
Billy

On Sat, Feb 28, 2009 at 8:30 PM, jonwood  wrote:

>
>
> John Stanton-3 wrote:
> >
> > Use the Sqlite date storage format and support.  With that approach
> > which is astronomivally correct you can deliver any date format or
> > manipulwtion,  You may need some custom written functions.  to get week
> > number according to national rules etc, but the method is sound.  It is
> > also compatible with different date systems.
> >
>
> Thanks, but I'm not sure what this means. "SQLite date storage format and
> support" doesn't appear to be a specific term (at least, it didn't turn up
> anything specific on Google). Is there a link that would describe what
> you're referring to and how it'd help my situation?
>
> Thanks.
>
> Jonathan
>
>
> --
> View this message in context:
> http://www.nabble.com/DateTime-Objects-tp22264879p22268988.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

John Stanton-3 wrote:
> 
> Use the Sqlite date storage format and support.  With that approach 
> which is astronomivally correct you can deliver any date format or 
> manipulwtion,  You may need some custom written functions.  to get week 
> number according to national rules etc, but the method is sound.  It is 
> also compatible with different date systems.
> 

Thanks, but I'm not sure what this means. "SQLite date storage format and
support" doesn't appear to be a specific term (at least, it didn't turn up
anything specific on Google). Is there a link that would describe what
you're referring to and how it'd help my situation?

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268988.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[John Stanton]

Use the Sqlite date storage format and support.  With that approach 
which is astronomivally correct you can deliver any date format or 
manipulwtion,  You may need some custom written functions.  to get week 
number according to national rules etc, but the method is sound.  It is 
also compatible with different date systems.

jonwood wrote:
>>> Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
>>> leading zeros, etc. Just as I pointed out in my original post.
>>>   
>> Well, I would say you (or your users) live in the past. 
>> The rest of the world uses ISO-8601 ;)
>> http://www.cl.cam.ac.uk/~mgk25/iso-time.html
>> 
>
> Heh, well, I suppose I could tell the client that I can't match his current
> reports, or make the columns narrow enough to all fit on a single page and
> see if he still wants to pay me. But, with the economy what it is, I was
> toying with the idea of just giving him what he asked for.
>
>   
>> Pun aside, you can always deliver epoch (or something else
>> you find more convenient) to your application and let the
>> application do the formatting. 
>> SQL isn't meant for presentation anyway, it's for relational
>> storage.
>>
>> Example:
>> Compute the time since the unix epoch in seconds (like
>> strftime('%s','now') except this includes the fractional
>> part):
>>
>> SELECT (julianday('now') - 2440587.5)*86400.0; 
>> 
>
> Right. I think I'm getting the picture of my options. I already have a fair
> amount of code working that relies on the a DATETIME column and was hoping
> it would support a date format supported by C/C++ (like the way I read
> DateTime values with MS SQL and C# in .NET).
>
> I can work something out if these are my options though.
>
> Thanks.
>
> Jonathan
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

Doug-4 wrote:
> 
> I personally store my times as ints (__time64_t, or time_t).  When I read
> it
> back my app formats it however I want.  Simple :)
> 

I think that's the conclusion I'm coming to as well.

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268713.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

D. Richard Hipp wrote:
> 
> The date & time come out in an easily parseable format: -MM-DD  
> HH:MM:SS.  So you call:
> 
>  sscanf(zDateStr, "%d-%d-%d %d:%d:%d", , , , , , );
>  sprintf(zNewDate, "%d/%d/%d %d:%d%s", m, d, y%100, (H-1)%12+1, M,  
> H>=12 ? "pm" : "am");
> 
> Is that really so hard?
> 

I don't recall anyone saying anything about being hard. It's a
design/efficiency issue.

I may do what you suggest only because there is code in place already that
uses a DATETIME column. But in the future I think I'd recommend storing my
own integer date format, as others here have suggested.

Thanks.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22268698.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Doug]

>it just seems very inefficient to store a date as a string in a database.

I agree.  But why would you store it as a string??

I personally store my times as ints (__time64_t, or time_t).  When I read it
back my app formats it however I want.  Simple :)

Doug


> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of jonwood
> Sent: Saturday, February 28, 2009 5:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] DateTime Objects
> 
> 
> 
> Jonas Sandman wrote:
> >
> > So store your time as a 64-bit integer. Sqlite has support for that.
> >
> 
> Yeah, I can either do something like that or do parsing with existing
> column. I was just taking advantage of some of the properties provided
> by
> the DATETIME column and it just seems very inefficient to store a date
> as a
> string in a database.
> 
> Thanks.
> 
> Jonathan
> 
> 
> --
> View this message in context: http://www.nabble.com/DateTime-Objects-
> tp22264879p22268085.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> 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] DateTime Objects

[D. Richard Hipp]

On Feb 28, 2009, at 6:16 PM, jonwood wrote:
>
> Yeah, I can do parsing with existing column.

The date & time come out in an easily parseable format: -MM-DD  
HH:MM:SS.  So you call:

 sscanf(zDateStr, "%d-%d-%d %d:%d:%d", , , , , , );
 sprintf(zNewDate, "%d/%d/%d %d:%d%s", m, d, y%100, (H-1)%12+1, M,  
H>=12 ? "pm" : "am");

Is that really so hard?

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Kees Nuyt]

On Sat, 28 Feb 2009 12:27:10 -0800 (PST), jonwood
 wrote:

>Derrell Lipman wrote:
>> 
>> http://sqlite.org/lang_datefunc.html
>> 
>
>Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
>leading zeros, etc. Just as I pointed out in my original post.

Well, I would say you (or your users) live in the past. 
The rest of the world uses ISO-8601 ;)
http://www.cl.cam.ac.uk/~mgk25/iso-time.html

Pun aside, you can always deliver epoch (or something else
you find more convenient) to your application and let the
application do the formatting. 
SQL isn't meant for presentation anyway, it's for relational
storage.

Example:
Compute the time since the unix epoch in seconds (like
strftime('%s','now') except this includes the fractional
part):

 SELECT (julianday('now') - 2440587.5)*86400.0; 


HTH
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Jonas]

So store your time as a 64-bit integer. Sqlite has support for that.

On 28 feb 2009, at 21.47, jonwood  wrote:

>
>> Database is for manipulating data. Your UI application is for  
>> presenting
>> it nicely to the user. After all, you don't complain that SQLite,  
>> say,
>> doesn't have functions for formatting numbers in user-friendly manner
>> (e.g. 123,456.78).
>
> So why does it have to be pre-formatted by storing it as text that I  
> must
> parse and then reformat? If it just stored a date object in binary
> format--as a database should do--then I could easily format it to  
> present to
> the user.
>
>> You can store dates as doubles representing Julian dates, or as  
>> integers
>> representing number of seconds since Unix epoch (aka time_t). Is this
>> the kind of bindary format you are talking about?
>
> Well, I don't know many CRT routines for working with Julian dates.  
> time_t
> has support but they've kind of moved to a 64-bit version. I guess I  
> could
> store it as a BLOB or store the year, month, day, hour, minute, and  
> second
> in separate fields as well. But that doesn't seem like a very good  
> approach
> to me.
>
> -- 
> View this message in context: 
> http://www.nabble.com/DateTime-Objects-tp22264879p22266629.html
> Sent from the SQLite mailing list archive at Nabble.com.
>
> ___
> 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] DateTime Objects

[Igor Tandetnik]

"jonwood"  wrote in
message news:22264879.p...@talk.nabble.com
> I have a SQLite table that contains a DATETIME value. However, the
> database does not appear to provide enough control over how that
> value is formatted. For example, I'd like a two-digit year, to
> eliminate leading zeros, and to show time using AM/PM format.

Database is for manipulating data. Your UI application is for presenting 
it nicely to the user. After all, you don't complain that SQLite, say, 
doesn't have functions for formatting numbers in user-friendly manner 
(e.g. 123,456.78).

> Is the ONLY way to accomplish this to manually parse the database
> value into separate values and then work with that? Is there no way
> to access these values in binary format without the overhead of
> convert to text and then back again?

You can store dates as doubles representing Julian dates, or as integers 
representing number of seconds since Unix epoch (aka time_t). Is this 
the kind of bindary format you are talking about?

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[jonwood]

Derrell Lipman wrote:
> 
> http://sqlite.org/lang_datefunc.html
> 

Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate
leading zeros, etc. Just as I pointed out in my original post.

Jonathan


-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22266413.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] DateTime Objects

[Derrell Lipman]

On Sat, Feb 28, 2009 at 1:47 PM, jonwood  wrote:

>
> Greetings,
>
> I have a SQLite table that contains a DATETIME value. However, the database
> does not appear to provide enough control over how that value is formatted.
> For example, I'd like a two-digit year, to eliminate leading zeros, and to
> show time using AM/PM format.
>

http://sqlite.org/lang_datefunc.html

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] DateTime Objects

[jonwood]

Greetings,

I have a SQLite table that contains a DATETIME value. However, the database
does not appear to provide enough control over how that value is formatted.
For example, I'd like a two-digit year, to eliminate leading zeros, and to
show time using AM/PM format.

Is the ONLY way to accomplish this to manually parse the database value into
separate values and then work with that? Is there no way to access these
values in binary format without the overhead of convert to text and then
back again?

Thanks.
-- 
View this message in context: 
http://www.nabble.com/DateTime-Objects-tp22264879p22264879.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users