Bug report: bug in datetime conversion function

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Bug report: bug in datetime conversion function

Fábio Pfeifer
Hello,

When working with Apple iOS databases, I found something strange when
dealing with dates. Apple iOS databases store dates as seconds from
2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29
there is a offset of one day if '31 years' modifier is used.
To reproduce, execute the following query:

select datetime(446860801, 'unixepoch', '31 years') as date1,
       datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2,
       datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3

date2 should be 1 day after date1, but shows as the same date!

Best regards,
Fábio Pfeifer
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug report: bug in datetime conversion function

Simon Slavin-3
On 18 Oct 2018, at 2:28pm, Fábio Pfeifer <[hidden email]> wrote:

> When working with Apple iOS databases, I found something strange when dealing with dates.

I suspect you are you are referring to a library routine which is used by lots of iOS software, but can you give us a specific App which exhibits this bug, and an easy way to find a database file from it ?

> Apple iOS databases store dates as seconds from
> 2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29 there is a offset of one day if '31 years' modifier is used.

I get the following:

SQLite version 3.24.0 2018-06-04 14:10:15
sqlite> select datetime(446860801, 'unixepoch', '31 years') as date1;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2;
2015-03-01 00:00:01
sqlite> SELECT datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3;
2015-03-02 00:00:01
sqlite>

Just for reference, 2015 was not a leap year, and 2016 was a leap year.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug report: bug in datetime conversion function

David Raymond
In reply to this post by Fábio Pfeifer
You have to look at the original dates. The date you're giving it for the first one is Feb 29th on a leap year. So 31 years from Feb 29th goes to Feb 29th on a NON-leap year, and thus gets rolled over to March 1st. For the second one you're saying 31 years from March 1st, which also lands on March 1st.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Fábio Pfeifer
Sent: Thursday, October 18, 2018 9:28 AM
To: [hidden email]
Subject: [sqlite] Bug report: bug in datetime conversion function

Hello,

When working with Apple iOS databases, I found something strange when
dealing with dates. Apple iOS databases store dates as seconds from
2001-01-01 (31 years after unix epoch). But from 2015-03-02 to 2016-02-29
there is a offset of one day if '31 years' modifier is used.
To reproduce, execute the following query:

select datetime(446860801, 'unixepoch', '31 years') as date1,
       datetime(446860801+60*60*24, 'unixepoch', '31 years') as date2,
       datetime(446860801+60*60*24*2, 'unixepoch', '31 years') as date3

date2 should be 1 day after date1, but shows as the same date!

Best regards,
Fábio Pfeifer
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users