I frequently have to deal with dates coming from R's "Date" class
which stores dates as the number of days since the UNIX epoch. So if
x is the number of days since 1970-01-01 then this gives the
yyyy-mm-dd representation of the date
date(x + 2440588)
and (annoyingly owing to the need for the 0.5) this is the inverse
(i.e. if y is the result of the above expression then this returns x):
julianday(y) - 2440588 + 0.5
The 2440588 comes from the following but it's a bit verbose when you
are dealing with a lot of dates to keep writing this out:
select julianday(date(0, "unixepoch"))
On Sat, Jan 30, 2016 at 9:31 AM, E.Pasma <pasma10 at concepts.nl> wrote:
> 30-01-2016 14:59, R Smith:
>
>>
>>
>> On 2016/01/30 3:22 PM, E.Pasma 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
>>
>> Thank you for the fix.
>>
>>>
>>> With respect to Igor's suggestion, yyyymmdd (as integer), why not leave out
>>> the century? I prefer the oldfashoned yymmdd.
>>
>> When dealing with a localized context around the current period, a two
>> digit date is often enough - so if you see '12 or '16 or '20 you can
>> easily assume that to mean 2012, 2016 etc. But what if you see '51?
>> Would that be 1951 or 2051?
>> The context would probably enlighten the meaning, but it's best to leave
>> context to the users and not pre-empt it during the design phase. A
>> four-digit year is best for standard human reference. (You won't need to
>> also add the AD. bit) :)
>>
> My private use is adding yymmdd as an extension to document names on my
> computer. You can leave it to me to know what 550501 refers to.
> Professionally this sounds very bad, agreed with the others. But a system
> may deal with a two-digit date according to clear rules. See the Oracle RR
> date format
>
> http://oracleeducation.blogspot.nl/2007/05/oracle-date-format_22.html
>
> But I may bring this up after a couple of decades again :-)
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com
