[sqlite] Best way to store only date

[Yannick Duchêne]

On Sat, 30 Jan 2016 14:22:06 +0100
"E.Pasma"  wrote:

> With respect to Igor's suggestion, mmdd (as integer), why not leave out
> the century? I prefer the oldfashoned yymmdd.

And what about a planned Y3K bug? -:D (teasing)

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

Why ?NO? for ?Needs to do calculations? with Igor's suggestion? Well, depends 
on what is doing the calculation, may be.

-- 
Yannick Duch?ne

[sqlite] Best way to store only date

[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, mmdd (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)  :)

[sqlite] Best way to store only date

[E.Pasma]

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, mmdd (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] Best way to store only date

[Igor Tandetnik]

On 1/30/2016 12:22 PM, Yannick Duch?ne wrote:
> Why ?NO? for ?Needs to do calculations? with Igor's suggestion? Well, depends 
> on what is doing the calculation, may be.

Things like "number of days elapsed between these two dates" is trivial 
with Julian day or similar, but MMDD representation needs to be 
parsed and converted first; simply subtracting two integers like this 
produces meaningless result.
-- 
Igor Tandetnik

[sqlite] Best way to store only date

[E.Pasma]

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, mmdd (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 (-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] Best way to store only date

[Simon Slavin]

On 30 Jan 2016, at 1:22pm, E.Pasma  wrote:

> With respect to Igor's suggestion, mmdd (as integer), why not leave out
> the century? I prefer the oldfashoned yymmdd.

While writing this program you develop a library for handling dates.  Then 
another program comes along for which some dates are in the past.  But now you 
can't use your library because it assumes '20yy'.  So now you have to write a 
new library, and then maintain two date libraries.

It doesn't take much space to store an extra two octets per date.  And it 
simplifies the code needed to do calculations and printing.

Simon.

[sqlite] Best way to store only date

[Gabor Grothendieck]

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
-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  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, mmdd (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

[sqlite] Best way to store only date

[Jim Callahan]

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

Let's not create a Y2100 problem; right after fixing Y2K!   ;)



This
email has been sent from a virus-free computer protected by Avast.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Sat, Jan 30, 2016 at 8:48 AM, Gerry Snyder 
wrote:

> On Jan 30, 2016 6:18 AM, "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
> >
> > With respect to Igor's suggestion, mmdd (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 (-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
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Best way to store only date

[Igor Tandetnik]

On 1/30/2016 8:22 AM, E.Pasma wrote:
> With respect to Igor's suggestion, mmdd (as integer), why not leave out
> the century? I prefer the oldfashoned yymmdd.

I too like to live dangerously.
-- 
Igor Tandetnik

[sqlite] Best way to store only date

[Gerry Snyder]

On Jan 30, 2016 6:18 AM, "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
>
> With respect to Igor's suggestion, mmdd (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 (-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

[sqlite] Best way to store only date

[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 (-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] Best way to store only date

[Jean-Christophe Deschamps]

At 16:23 29/01/2016, you wrote:
>Note that the fractional part in Julian day represents time-of-day 
>since noon, not since midnight.

Ah yes, I often forget about this point. Sorry for incorrect lead.

[sqlite] Best way to store only date

[Cecil Westerhof]

2016-01-29 16:23 GMT+01:00 Igor Tandetnik :

> On 1/29/2016 2:39 AM, Jean-Christophe Deschamps wrote:
>
>> select cast(julianday('now') as int)
>>
>> should do what you want.
>>
>
> Note that the fractional part in Julian day represents time-of-day since
> noon, not since midnight. The expression above will give different values
> at 11am and 1pm (UTC) of the same day, and the same value at 11pm and 1am
> of the same night. You would probably want cast(julianday('now')-0.5 as
> int) or perhaps cast(julianday('now', 'localtime')-0.5 as int)
>

?Yeah, I saw that. I went back to:
dateTEXT NOT NULL DEFAULT CURRENT_DATE
?



> Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other
> words, storing calendar dates as integers like 20160129.
>

?That is a nice one. Maybe I'll change it again. ;-)

-- 
Cecil Westerhof

[sqlite] Best way to store only date

[Igor Tandetnik]

On 1/29/2016 2:39 AM, Jean-Christophe Deschamps wrote:
> select cast(julianday('now') as int)
>
> should do what you want.

Note that the fractional part in Julian day represents time-of-day since 
noon, not since midnight. The expression above will give different 
values at 11am and 1pm (UTC) of the same day, and the same value at 11pm 
and 1am of the same night. You would probably want 
cast(julianday('now')-0.5 as int) or perhaps cast(julianday('now', 
'localtime')-0.5 as int)

Personally, I prefer cast(strftime('%Y%m%d', 'now') as int) - in other 
words, storing calendar dates as integers like 20160129.
-- 
Igor Tandetnik

[sqlite] Best way to store only date

[Cecil Westerhof]

2016-01-29 8:39 GMT+01:00 Jean-Christophe Deschamps :

> At 08:28 29/01/2016, you wrote:
>
>> For storing a date I probably could use strftime('%J'). But I would like
>> to
>> store it as an INT. But I see no way to cast a float to an int. Is that
>> not
>> possible?
>>
>
> select cast(julianday('now') as int)
>
> should do what you want.
>

?It does: thanks.?



-- 
Cecil Westerhof

[sqlite] Best way to store only date

[Jean-Christophe Deschamps]

At 08:28 29/01/2016, you wrote:
>For storing a date I probably could use strftime('%J'). But I would 
>like to
>store it as an INT. But I see no way to cast a float to an int. Is 
>that not
>possible?

select cast(julianday('now') as int)

should do what you want.

[sqlite] Best way to store only date

[Cecil Westerhof]

To store date and time at the moment I use:
datetimeINT NOT NULL DEFAULT (strftime('%s'))

For storing a date I probably could use strftime('%J'). But I would like to
store it as an INT. But I see no way to cast a float to an int. Is that not
possible?

Or is it better just keeping to use:
dateTEXT NOT NULL DEFAULT CURRENT_DATE

-- 
Cecil Westerhof