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
