Re: [sqlite] Bug in date() function ??

[Pavel Ivanov]

It looks pretty interesting that no matter if date() function works as
is now or with some heuristics applied the following equations are not
always true:

date(some_date, '-1 month', '+1 month') = some_date
date(some_date, '-1 month') = date(some_date, '-1 day', '-1 month', '+1 day')

Looks like a lot of head ache for someone trying to work with such
arithmetics in his application. :)


Pavel

On Tue, Oct 13, 2009 at 3:43 PM, Nicolas Williams
 wrote:
> On Tue, Oct 13, 2009 at 03:30:44PM -0400, D. Richard Hipp wrote:
>> Begin with 2001-03-31
>> Add 1 to 03, yielding 2001-04-31
>> 04-31 means the 31st day from the beginning of april: 2001-05-01
>>
>> Begin with 2001-03-31
>> Subtract 1 from 03 yielding 2001-02-31.
>> 02-31 means the 31st day from the beginning of february:  2001-03-03
>
> The fact that Earth years are not a whole multiple of some convenient
> number of Earth days (i.e., months), is certainly annoying.  What
> SQLite3 does seems perfectly justified; that it may sometimes seem
> surprising is not your fault, but cosmic chance.
>
> I think the OP expected that 2001-03-31 - 1month == 2001-02-28 (or 29,
> on leap years), because that's often (but not always) what people mean
> when they say "a month ago".  You could have a lot of special casing in
> date() to get something closer to what people normally mean by "a month
> ago", but it'd be alot harder to explain the many heuristic choices, and
> the choices might be too specific to one language/culture -- that'd not
> be worthwhile, IMO.
>
> Nico
> --
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in date() function ??

[Nicolas Williams]

On Tue, Oct 13, 2009 at 03:30:44PM -0400, D. Richard Hipp wrote:
> Begin with 2001-03-31
> Add 1 to 03, yielding 2001-04-31
> 04-31 means the 31st day from the beginning of april: 2001-05-01
> 
> Begin with 2001-03-31
> Subtract 1 from 03 yielding 2001-02-31.
> 02-31 means the 31st day from the beginning of february:  2001-03-03

The fact that Earth years are not a whole multiple of some convenient
number of Earth days (i.e., months), is certainly annoying.  What
SQLite3 does seems perfectly justified; that it may sometimes seem
surprising is not your fault, but cosmic chance.

I think the OP expected that 2001-03-31 - 1month == 2001-02-28 (or 29,
on leap years), because that's often (but not always) what people mean
when they say "a month ago".  You could have a lot of special casing in
date() to get something closer to what people normally mean by "a month
ago", but it'd be alot harder to explain the many heuristic choices, and
the choices might be too specific to one language/culture -- that'd not
be worthwhile, IMO.

Nico
-- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in date() function ??

[D. Richard Hipp]

On Oct 13, 2009, at 3:17 PM, Keith Roberts wrote:

> On Tue, 13 Oct 2009, D. Richard Hipp wrote:
>
>> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
>> From: D. Richard Hipp <d...@hwaci.com>
>> Subject: Re: [sqlite] Bug in date() function ??
>>
>>
>> On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:
>>
>>> Just been messing about with the date functions, and there
>>> appears to be an inconsistency when adding a month
>>> modifier. I'm running Fedora 10.
>>>
>>> From: http://www.sqlite.org/lang_datefunc.html
>>>
>>> "Thus, for example, the data 2001-03-31 modified by '+1
>>> month' initially yields 2001-04-31, but April only has 30
>>> days so the date is normalized to 2001-05-01."
>>
>> Did you happen to read the previous sentence in the documentation?
>
> Yes, I've read it again Richard. As the '+1 month' modifier
> modified the date by adding 31 days to normalise the date
> upwards, I expected the '-1 month' modifier to work in a
> similar fashion, and normalise the date *downwards* also by
> an interval of 31 days.

Begin with 2001-03-31
Add 1 to 03, yielding 2001-04-31
04-31 means the 31st day from the beginning of april: 2001-05-01

Begin with 2001-03-31
Subtract 1 from 03 yielding 2001-02-31.
02-31 means the 31st day from the beginning of february:  2001-03-03

>
> Kind Regards,
>
> Keith Roberts
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in date() function ??

[Keith Roberts]

On Tue, 13 Oct 2009, D. Richard Hipp wrote:

> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> From: D. Richard Hipp <d...@hwaci.com>
> Subject: Re: [sqlite] Bug in date() function ??
> 
>
> On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:
>
>> Just been messing about with the date functions, and there
>> appears to be an inconsistency when adding a month
>> modifier. I'm running Fedora 10.
>>
>> From: http://www.sqlite.org/lang_datefunc.html
>>
>> "Thus, for example, the data 2001-03-31 modified by '+1
>> month' initially yields 2001-04-31, but April only has 30
>> days so the date is normalized to 2001-05-01."
>
> Did you happen to read the previous sentence in the documentation?

Yes, I've read it again Richard. As the '+1 month' modifier 
modified the date by adding 31 days to normalise the date 
upwards, I expected the '-1 month' modifier to work in a 
similar fashion, and normalise the date *downwards* also by 
an interval of 31 days.

Kind Regards,

Keith Roberts
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in date() function ??

[Igor Tandetnik]

Keith Roberts  wrote:
> From: http://www.sqlite.org/lang_datefunc.html
> 
> "Thus, for example, the data 2001-03-31 modified by '+1
> month' initially yields 2001-04-31, but April only has 30
> days so the date is normalized to 2001-05-01."
> 
> When I add '+1 month' to the example data, the example date
> has 31 days added to it. Which is correct.
> 
> When I subtract '-1 month' the date only gets 28 days
> subtracted from it.

By the same logic, subtracting one month from 2001-03-31 leads to 2001-02-31, 
but Feburary only has 28 days, so this gets normalized to 2001-03-03. It's not 
about the number of days. If you want to add or subtract a particular number of 
days, say so.

> Surely adding or subtracting a month modifier to a date
> should be the same number of days in each direction?

If that were the case, then these two equalities could not be both true:

date('2001-03-01', '-1 month') = '2001-02-01'
date('2001-03-01', '+1 month') = '2001-04-01'

Which one of the above do you believe is incorrect?

Igor Tandetnik


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in date() function ??

[D. Richard Hipp]

On Oct 13, 2009, at 12:57 PM, Keith Roberts wrote:

> Just been messing about with the date functions, and there
> appears to be an inconsistency when adding a month
> modifier. I'm running Fedora 10.
>
> From: http://www.sqlite.org/lang_datefunc.html
>
> "Thus, for example, the data 2001-03-31 modified by '+1
> month' initially yields 2001-04-31, but April only has 30
> days so the date is normalized to 2001-05-01."

Did you happen to read the previous sentence in the documentation?

>
> When I add '+1 month' to the example data, the example date
> has 31 days added to it. Which is correct.
>
> When I subtract '-1 month' the date only gets 28 days
> subtracted from it.
>
> Surely adding or subtracting a month modifier to a date
> should be the same number of days in each direction?
>

D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug in date() function ??

[Keith Roberts]

Just been messing about with the date functions, and there 
appears to be an inconsistency when adding a month 
modifier. I'm running Fedora 10.

[root ~]# sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> SELECT date('2001-03-31');
2001-03-31

sqlite> SELECT date('2001-03-31', '+1 month');
2001-05-01

sqlite> SELECT date('2001-03-31', '+31 day');
2001-05-01

sqlite> SELECT date('2001-03-31', '-31 day');
2001-02-28

sqlite> SELECT date('2001-03-31', '-1 month');
2001-03-03

sqlite> SELECT date('2001-03-31', '-28 day');
2001-03-03

sqlite>

From: http://www.sqlite.org/lang_datefunc.html

"Thus, for example, the data 2001-03-31 modified by '+1 
month' initially yields 2001-04-31, but April only has 30 
days so the date is normalized to 2001-05-01."

When I add '+1 month' to the example data, the example date 
has 31 days added to it. Which is correct.

When I subtract '-1 month' the date only gets 28 days 
subtracted from it.

Surely adding or subtracting a month modifier to a date 
should be the same number of days in each direction?

Kind Regards,

Keith Roberts

-
Websites:
http://www.php-debuggers.net
http://www.karsites.net
http://www.raised-from-the-dead.org.uk

All email addresses are challenge-response protected with
TMDA [http://tmda.net]
-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users