On Jan 30, 2016 6:18 AM, "E.Pasma" <pasma10 at concepts.nl> wrote:
>
> The diagram got broken in my email and here is another try:
>
> Needs to be light | Needs to be | Needs to do |
> (small footprint) | Human-Readable | calculations |
> ----------------- | ---------------| ------------ |
> YES | YES | NO | Integer as
> | | | Igor's suggestion
> | | |
> YES | NO | YES | Float/Int
> | | | Julianday
> | | |
> NO | YES | YES | Datetime/Numeric
> | | | ISO Standard
>
> With respect to Igor's suggestion, yyyymmdd (as integer), why not leave
out
> the century? I prefer the oldfashoned yymmdd.
The advantage of the four-digit year is that it can be used for sorting
over a wide range.
Gerry
>
> Thanks, E. Pasma
> 30-01-2016 00:31, R Smith:
>
> >
> > On 2016/01/29 5:23 PM, Igor Tandetnik wrote:
> >>
> >> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
> >> words, storing calendar dates as integers like 20160129.
> >
> > The main advantage of this format is that it is of course
> > human-readable, even as an integer.
> > The important disadvantage is that you cannot do date calculations
> > without first casting and translating - something the Julian day or more
> > expensive 19-char ISO format (YYYY-MM-DD HH:NN:SS which is
> > human-readable AND in most systems calculatable) is better at.
> >
> > My point being: when I decide which date format to use, I first try to
> > establish whether I will use it for calculations or simply record/log
> > purposes, and if readability (from data source) would be needed/helpful
> > or not. The decision matrix ends up something like this:
> >
> >
> > Needs to be light (small footprint)| Needs to be Human-Readable
> > | Needs to do calculations |
> > ---------------------------------- | ----------------------------------
> > | ---------------------------------- |
----------------------------------
> > YES | YES |
> > NO | Integer (as Igor's suggestion)
> > YES | NO |
> > YES | Float/Int Julianday
> > NO | YES |
> > YES | Datetime/Numeric ISO Standard
> > ---------------------------------- | ----------------------------------
> > | ---------------------------------- |
----------------------------------
> >
> > If you can say "No" to two of these criteria, go for the most efficient.
> >
> > If you can say "No" to all three criteria, perhaps reconsider whether
> > you really need that column in your table.
> >
> >
> > Cheers,
> > Ryan
> >
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
