I have a php script that saves in a sqlite database some schedules. It always worked until I noticed nearly two weeks ago that sqlite had a weird behavior. I save the dates in a text field and the records can't be ordered by date.
For example, I have some recent added dates for February and if I run the query to return the dates after August 22nd:
SELECT `date`, hex(`date`), substr(`date`, 1, 1), substr(`date`, 1)
FROM Schedules WHERE `date` > '2019-08-22' order by `date`
I get the records:
`date` hex(`date`) substr(`date`, 1, 1) substr(`date`, 1)
2019-08-22 13:00 323031392D30382D32322031333A3030 2 2019-08-22 13:00
2019-02-04 12:00 323031392D30322D30342031323A3030 2 2019-02-04 12:00
2019-02-18 12:00 323031392D30322D31382031323A3030 2 2019-02-18 12:00
2019-03-04 12:00 323031392D30332D30342031323A3030 2 2019-03-04 12:00
The first record is correct, I included it to have a comparison with the wrong ones. I thought about having some chars before the actual number but from the hex column it doesn't seem so. I even tried to request dates greater than '3000' to exclude the dash (maybe they had some unicode char) and it keep returning the last three records of the previous results.
The table is defined as:
CREATE TABLE `Schedules` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
`date` TEXT
);
My php code to add the record is:
class Schedule {
public static function add_schedule($programmed_date, $programmed_time, $options) {
$result = 0;
date_default_timezone_set('Europe/Rome');
$date = DateTime::createFromFormat('d/m/Y H:i', "$programmed_date $programmed_time");
$now = new DateTime();
if ($date < $now)
return -1;
$dbh = self::connect_to_database();
$stmt = $dbh->prepare('INSERT INTO Schedules(`date`) VALUES (:date)');
$stmt->bindValue(':date', $date->format('Y-m-d H:i'), SQLITE3_TEXT);
try {
$execution = $stmt->execute();
if ($execution !== FALSE)
$result = 1;
}
catch (Exception $ex) {}
finally {
$stmt->closeCursor();
}
$dbh = null;
return $result;
}
private static function connect_to_database() {
$db_path = Credentials::read_info('schedule')['db_path'];
try {
$dbh = new PDO('sqlite:'.$db_path);
$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo 'Connection failed: '.$e->getMessage();
exit;
}
return $dbh;
}
}
At this point I can't guess what's wrong with the code. The sql statement should return only the first record instead of four. Any thoughts? Thank you.
SQLite DateTime comparison led me to https://www.sqlite.org/lang_datefunc.html and I found a workaround, if I run the query:
SELECT `date`, hex(`date`), substr(`date`, 1, 1), substr(`date`, 1)
FROM Schedules WHERE datetime(`date`) > datetime('2019-08-22') order by `date`
It works correctly returning only the record for August 22nd. However I keep wondering why the plain text comparison and order screws up the results when here https://www.db-fiddle.com/f/ar9JxpaCo2wcQB1MobyaVv/0 there is a dump and you clearly see that the first query I wrote here returns a different (and correct) result. If you want to try directly to my database you can download it from here
