2016-05-05 17:09 GMT+02:00 Cecil Westerhof <cldwesterhof at gmail.com>:
> 2016-05-05 15:36 GMT+02:00 Adam Devita <adevita at verifeye.com>:
>
>> 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
