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