[sqlite] strftime accepts an illegal time string
> >For instance, do you care if someone enters a time which is skipped by the >clocks going forward ? If at 1am your clocks skip straight to 2am, do you >care if someone enters a time of 1:30am on that >day ? > > >Simon. > Our local time skips from 2am to 3am and from 3am back to 2am for DST. This caused a job scheduled to run a 2:30 am to be skipped going into DST. But we made up by executing it twice when setting the clocks back ;) ___ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
[sqlite] strftime accepts an illegal time string
2016-05-05 17:09 GMT+02:00 Cecil Westerhof :
> 2016-05-05 15:36 GMT+02:00 Adam Devita :
>
>> What would be the 'correct' behaviour for an out of bounds day in a
>> month? If you look at dates as an index + natural number offset then Jan
>> 32 == Feb 1.What is January 0 or January -1?
>>
>
> ?Well, if my memory is correct, that is the way MySQL does it. I liked
> that, but because at the moment 32 is always an illegal value I only
> propose to make 30 and 31 illegal for February. ?It would also be nice to
> make 29 illegal when it is not a leap year. And 31 for months that only
> have 30 days.
>
> It is even possible with SQLite. See the end of the post.
>
?I should have done some more checks. :'-(
And I think the solution could even be reasonable simple. When executing:
>
> ?WITH datevalue AS (
> SELECT '2004-04-31 23:59:00' dateString
> )
> SELECT dateString
> , strftime('%Y-%m-%d %H:%M', dateString)
> AS strftime
> , strftime('%s', dateString)
> AS epochtime
> , strftime('%Y-%m-%d %H:%M', strftime('%s', dateString), 'unixepoch')
> AS strftime2
> FROM datevalue
> ;
>
> you get:
> ?dateString strftime epochtime strftime2
> --- --
> 2004-04-31 23:59:00 2004-04-31 23:59 1083455940 2004-05-01 23:59
>
> By creating the epochtime you could get the ?real? date like in MySQL, but
> this is then also the solution for validating strftime. Internally
> strftime2 should also be done and if that gives a different value as
> strftime, then strftime should return NULL.
>
?This part still works. But I think epochtime should also return null. See
below.
?
> That should not to hard to implement I would think.
>
> ?With an extra parameter strftime could be made to return ??2004-05-01
> 23:59? instead of null. That would be a nice enhancement also I think and
> would not break backward compatibility.
>
?This is a bad idea. Because when I use the following:
WITH datevalue AS (
SELECT '2004-04-32 23:59:00' AS dateString, '%Y-%m-%d %H:%M' AS
formatString
)
SELECT dateString
, strftime(formatString, dateString) AS
strftime
, strftime('%s', dateString) AS
epochtime
, strftime(formatString, strftime('%s', dateString), 'unixepoch') AS
strftime2
FROM datevalue
;
I get three NULL's. Maybe something where ?all? values give an updated
result back. But that could be a ?little? more difficult.
--
Cecil Westerhof
[sqlite] strftime accepts an illegal time string
2016-05-05 15:36 GMT+02:00 Adam Devita :
> What would be the 'correct' behaviour for an out of bounds day in a
> month? If you look at dates as an index + natural number offset then Jan
> 32 == Feb 1.What is January 0 or January -1?
>
?Well, if my memory is correct, that is the way MySQL does it. I liked
that, but because at the moment 32 is always an illegal value I only
propose to make 30 and 31 illegal for February. ?It would also be nice to
make 29 illegal when it is not a leap year. And 31 for months that only
have 30 days.
It is even possible with SQLite. See the end of the post.
If expressing dates this way (and not as an int from an epoch) I think
> that it is up to the application to sanitize inputs for this one.About
> 8 years ago I decided that I was not going to to use the sqlite date
> functions at all, preferring to either store string dates for humans or the
>
?I am not doing it either, but other people do.
?
> numeric value (unix epoch, or .net timestamp ), and write it into the
> design rules for the project that only UTC date values will be in the db.
>
?I also only work with UTC values. Makes thing a little more complicated,
but I do not mind.?
?For example I made yesterday a service that stores the CPU usage of
Firefox in a SQLite table every minute. When the day changes I want to
generate a summary. This means I have to ask the date from SQLite, but that
is not to difficult:
sqlite3 <<<"SELECT CURRENT_DATE;"
And again: I am only talking about UTC.
?
> Every once in a while there are some very interesting lessons about the
> precision of time on the list (the Earth used to turn faster) etc. Others
> can talk about extensions / modules handles in the extreme cases. I
> wouldn't suggest that we drop the date time functions due to a predictable
> storm of "we will not break backward compatibility", but I wouldn't care if
> sqlite dropped date formatting altogether. (At this point I do not need
> instructions as to compiling it out so save a few KB on the device.)
>
?Well, there are people using it. (And probably a lot.) So if with a little
effort the quality would significantly improve (and I am not talking about
making it perfect), then I think it would be worth the effort.
> People on the list are genuinely trying to be helpful, even if cross
> language text comes across as terse, try to keep that context.
>
?Well, I do not think it is cross language, but straw man. (I like neither,
but the second is even worse I think.) Saying that what I suggest is
impossible by pretending that I suggest something completely different.
Another thing is the discussion I started about NULL in primary keys. I was
a little bit to enthusiastic and I was wrong. I have no problem
acknowledging that. But also in this case it started wrongly. I was told
that I should read the documentation and not think that SQLite does the
things like other databases. I had read the documentation and the
documentation showed that the maintainers would like to prohibit NULL's in
primary keys. (That was why I started the discussion.)
If there is a reason to not do something, use real arguments. I do not mind
them and in the long run it is better for everyone. It takes initially a
little more time, but it is best in the long run. You can build a house
faster when you do not lay down a foundation, but I do not think it is a
good idea.
And I think the solution could even be reasonable simple. When executing:
?WITH datevalue AS (
SELECT '2004-04-31 23:59:00' dateString
)
SELECT dateString
, strftime('%Y-%m-%d %H:%M', dateString)
AS strftime
, strftime('%s', dateString)
AS epochtime
, strftime('%Y-%m-%d %H:%M', strftime('%s', dateString), 'unixepoch')
AS strftime2
FROM datevalue
;
you get:
?dateString strftime epochtime strftime2
--- --
2004-04-31 23:59:00 2004-04-31 23:59 1083455940 2004-05-01 23:59
By creating the epochtime you could get the ?real? date like in MySQL, but
this is then also the solution for validating strftime. Internally
strftime2 should also be done and if that gives a different value as
strftime, then strftime should return NULL.
That should not to hard to implement I would think.
?With an extra parameter strftime could be made to return ??2004-05-01
23:59? instead of null. That would be a nice enhancement also I think and
would not break backward compatibility.
Of-course it can not be implemented immediately, things has to be thought
over before doing something, to circumvent being bitten, but I think it is
worth it.
--
Cecil Westerhof
[sqlite] strftime accepts an illegal time string
2016-05-05 12:39 GMT+02:00 Simon Slavin : > > On 5 May 2016, at 11:25am, Cecil Westerhof wrote: > > > At > > the moment valid times can be marked as invalid and invalid times as > valid. > > Probably imposable to completely circumvent, but it can be done a lot > > better. > > I don't know what TimeZone you're in (your surname looks German) but at > this level of detail it becomes ?German would be Westerhoff. I live in the Netherlands. ? > important whether you're in the US or EU or any other place. The phrase > 'valid times' covers a large number of subjects and we can't tell which of > them are important to you. > > For instance, do you care if someone enters a time which is skipped by the > clocks going forward ? If at 1am your clocks skip straight to 2am, do you > care if someone enters a time of 1:30am on that day ? > > Or maybe you're in Samoa, which skipped the 30th of December 2011 > entirely, and may one day want to go the other way, which it would do by > having a 32nd of December or an unwarranted 29th of February. > > You can get endlessly fussy about leap years and leap seconds and such > things to the point where you know the Time Lords by name. SQLite > definitely cannot handle that level of detail and it should not be used for > timestamp validation. It's best either to find an external library for > your programming language or tell yourself to relax and stop sweating the > small stuff. ?I do not like the straw man stuff. https://en.wikipedia.org/wiki/Straw_man I respond to a question and in the response from someone else something is said that when checked is proved to be not true. I do then some other checking and find some things that could in my opinion easily be rectified. I can understand a reaction like: We do not want to change it because we do not find it important. What I really not like is: We do not want to change it, but do not want to acknowledge it. So lets pretend that what is asked is unreasonable. I never was talking about timezones, so the problems about Samao and Summer Time has nothing to do with what I was talking about. I was not ?endlessly fussy?, I was only talking about a few simple changes that would give a much better result with little effort (Pareto Principle). Again: it is possible that it is something that the maintainers not want to do, but be honest about that. I like to contribute, but a treatment like this is not encouraging. -- Cecil Westerhof
[sqlite] strftime accepts an illegal time string
2016-05-05 10:08 GMT+02:00 R Smith :
>
>
> On 2016/05/05 4:26 AM, Cecil Westerhof wrote:
>
>> The statement:
>> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-31 17:19:59.670')
>> gives:
>> 2016-04-31 17:19:59.670
>>
>> Should that not be NULL?
>>
>> It does with:
>> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-32 17:19:59.670')
>>
>> It looks like a value of 31 is always allowed for day:
>> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-02-31 17:19:59.670')
>> gives:
>> 2016-02-31 17:19:59.670
>>
>>
> Yes - Keith's suggestion would work best if you want to check whether the
> STRING you check is a valid Date-Time STRING, that's very different from
> checking whether it is a valid actual Date and Time. The Notion of checking
> valid dates is a lot more complicated, and as Simon has pointed out, should
> rather be done in your code since you can then decide to what level you
> wish to check.
>
> To name a few idiosyncrasies - Sometimes on a year with a leap-second, the
> date-string '2004-12-31 23:59:60' is an actual valid date-time, while the
> very next year, that would not be valid seconds. Leap years themselves also
> have problems - the easiest check is to see if the year is divisible by 4
> and then allow a 29th on Feb, but of course for the year 1900 this would
> have been wrong, but for 2000 this is right again, etc. Then there starts
> the problem of confusing whole days when getting close to the
> Julian/Gregorian range boundaries, or when Samoa skips a whole day, if your
> application use historic dates, and so on.
>
?OK, it cannot be perfect, but I think it could be a lot better with little
work. For example:
2004-12-31 23:59:60
is a valid date-time, but gives back NULL. Seeing how liberal days are
handled I think that:
-12-31 23:59:60
always should be accepted. Except when itself is not acceptable. At
the moment valid times can be marked as invalid and invalid times as valid.
Probably imposable to completely circumvent, but it can be done a lot
better.
For February I think 30 and 31 is never an acceptable value, as is 31 for
April, June, ? As Stephan Beal commented the days in a year can be used to
verify if 29 February is allowed.
These changes would not be much work I think, but would improve the quality
of strftime a lot.
--
Cecil Westerhof
[sqlite] strftime accepts an illegal time string
On 5 May 2016, at 11:25am, Cecil Westerhof wrote: > At > the moment valid times can be marked as invalid and invalid times as valid. > Probably imposable to completely circumvent, but it can be done a lot > better. I don't know what TimeZone you're in (your surname looks German) but at this level of detail it becomes important whether you're in the US or EU or any other place. The phrase 'valid times' covers a large number of subjects and we can't tell which of them are important to you. For instance, do you care if someone enters a time which is skipped by the clocks going forward ? If at 1am your clocks skip straight to 2am, do you care if someone enters a time of 1:30am on that day ? Or maybe you're in Samoa, which skipped the 30th of December 2011 entirely, and may one day want to go the other way, which it would do by having a 32nd of December or an unwarranted 29th of February. You can get endlessly fussy about leap years and leap seconds and such things to the point where you know the Time Lords by name. SQLite definitely cannot handle that level of detail and it should not be used for timestamp validation. It's best either to find an external library for your programming language or tell yourself to relax and stop sweating the small stuff. Simon.
[sqlite] strftime accepts an illegal time string
On Thu, May 5, 2016 at 10:08 AM, R Smith wrote:
> seconds. Leap years themselves also have problems - the easiest check is
> to see if the year is divisible by 4 and then allow a 29th on Feb, but of
> course for the year 1900 this would have been wrong, but for 2000 this is
> right again, etc
i think this is easier: check if the year as 365 or 366 days:
sqlite> select strftime('%j', '2016-12-31');
366
sqlite> select strftime('%j', '2015-12-31');
365
with the usual caveats for dates in the far past.
--
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
[sqlite] strftime accepts an illegal time string
On 2016/05/05 4:26 AM, Cecil Westerhof wrote:
> The statement:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-31 17:19:59.670')
> gives:
> 2016-04-31 17:19:59.670
>
> Should that not be NULL?
>
> It does with:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-32 17:19:59.670')
>
> It looks like a value of 31 is always allowed for day:
> SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-02-31 17:19:59.670')
> gives:
> 2016-02-31 17:19:59.670
>
Yes - Keith's suggestion would work best if you want to check whether
the STRING you check is a valid Date-Time STRING, that's very different
from checking whether it is a valid actual Date and Time. The Notion of
checking valid dates is a lot more complicated, and as Simon has pointed
out, should rather be done in your code since you can then decide to
what level you wish to check.
To name a few idiosyncrasies - Sometimes on a year with a leap-second,
the date-string '2004-12-31 23:59:60' is an actual valid date-time,
while the very next year, that would not be valid seconds. Leap years
themselves also have problems - the easiest check is to see if the year
is divisible by 4 and then allow a 29th on Feb, but of course for the
year 1900 this would have been wrong, but for 2000 this is right again,
etc. Then there starts the problem of confusing whole days when getting
close to the Julian/Gregorian range boundaries, or when Samoa skips a
whole day, if your application use historic dates, and so on.
There are large libraries out there that will do all of this checking,
but you have to decide how much you care about the veracity of date
inputs and check accordingly. SQLite is not the tool/library to do this
with.
For some comic relief re computer date and time calculation problems, see:
https://www.youtube.com/watch?v=-5wpm-gesOY
[sqlite] strftime accepts an illegal time string
What would be the 'correct' behaviour for an out of bounds day in a month? If you look at dates as an index + natural number offset then Jan 32 == Feb 1.What is January 0 or January -1? If expressing dates this way (and not as an int from an epoch) I think that it is up to the application to sanitize inputs for this one.About 8 years ago I decided that I was not going to to use the sqlite date functions at all, preferring to either store string dates for humans or the numeric value (unix epoch, or .net timestamp ), and write it into the design rules for the project that only UTC date values will be in the db. Within the bounds of time we use (I don't have to worry about handling product construction or delivery dates before the company's founding) this is ok. This smooths out time to simplify calculations and shoves the formatting & presentation of the data to the application's presentation layer, which knows things like what time zone the user is sitting in, and their cultural context. Every once in a while there are some very interesting lessons about the precision of time on the list (the Earth used to turn faster) etc. Others can talk about extensions / modules handles in the extreme cases. I wouldn't suggest that we drop the date time functions due to a predictable storm of "we will not break backward compatibility", but I wouldn't care if sqlite dropped date formatting altogether. (At this point I do not need instructions as to compiling it out so save a few KB on the device.) People on the list are genuinely trying to be helpful, even if cross language text comes across as terse, try to keep that context. best wishes, Adam DeVita On Thu, May 5, 2016 at 8:52 AM, Cecil Westerhof wrote: > 2016-05-05 12:39 GMT+02:00 Simon Slavin : > > > > > On 5 May 2016, at 11:25am, Cecil Westerhof > wrote: > > > > > At > > > the moment valid times can be marked as invalid and invalid times as > > valid. > > > Probably imposable to completely circumvent, but it can be done a lot > > > better. > > > > I don't know what TimeZone you're in (your surname looks German) but at > > this level of detail it becomes > > > ?German would be Westerhoff. I live in the Netherlands. > > ? > > > > important whether you're in the US or EU or any other place. The phrase > > 'valid times' covers a large number of subjects and we can't tell which > of > > them are important to you. > > > > For instance, do you care if someone enters a time which is skipped by > the > > clocks going forward ? If at 1am your clocks skip straight to 2am, do > you > > care if someone enters a time of 1:30am on that day ? > > > > Or maybe you're in Samoa, which skipped the 30th of December 2011 > > entirely, and may one day want to go the other way, which it would do by > > having a 32nd of December or an unwarranted 29th of February. > > > > You can get endlessly fussy about leap years and leap seconds and such > > things to the point where you know the Time Lords by name. SQLite > > definitely cannot handle that level of detail and it should not be used > for > > timestamp validation. It's best either to find an external library for > > your programming language or tell yourself to relax and stop sweating the > > small stuff. > > > ?I do not like the straw man stuff. > https://en.wikipedia.org/wiki/Straw_man > > I respond to a question and in the response from someone else something is > said that when checked is proved to be not true. I do then some other > checking and find some things that could in my opinion easily be rectified. > I can understand a reaction like: > We do not want to change it because we do not find it important. > What I really not like is: > We do not want to change it, but do not want to acknowledge it. So lets > pretend that what is asked is unreasonable. > > I never was talking about timezones, so the problems about Samao and Summer > Time has nothing to do with what I was talking about. > I was not ?endlessly fussy?, I was only talking about a few simple changes > that would give a much better result with little effort (Pareto Principle). > Again: it is possible that it is something that the maintainers not want to > do, but be honest about that. > > > I like to contribute, but a treatment like this is not encouraging. > > -- > Cecil Westerhof > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- -- VerifEye Technologies Inc. 151 Whitehall Dr. Unit 2 Markham, ON L3R 9T1
[sqlite] strftime accepts an illegal time string
The statement:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-31 17:19:59.670')
gives:
2016-04-31 17:19:59.670
Should that not be NULL?
It does with:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-04-32 17:19:59.670')
It looks like a value of 31 is always allowed for day:
SELECT strftime('%Y-%m-%d %H:%M:%f', '2016-02-31 17:19:59.670')
gives:
2016-02-31 17:19:59.670
--
Cecil Westerhof
