asked on

SQLite date time

I'm trying to setup a database in SQLite (Im used to MySQL), but a lot of the data uses date and times which SQLite doesn't support.

So from what I know I need to use a text field or numeric field to store dates, however I don't understand how to search these date for rows between certain dates or rows from today's date.

I know the most probable thing to do is stay with MySQL instead of SQLite, but I like the idea of using SQLite for portability.

Anyone got any suggestions on using SQLite for DateTime fields?

Thank you

Ray Paseur

You can store the date/time values in either TEXT if you use ISO-8601 values, or INTEGER if you use Unix timestamps. The Unix timestamps might be more flexible, since you would not have to be concerned about the timezones your clients use - just normalize everything to UTC. I have some SQLite examples, and maybe I can modify one to show you how to search for dates or dates "between" values.

You probably want to carry only datetime values; if you separate the dates and times into different columns you could create a recipe for confusion.

Dave Baldwin

This page https://www.sqlite.org/datatype3.html describes how SQLite handles data types including Date and Time datatypes. It is not the same as MySQL in many cases.

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dave Baldwin

Nice demo. I had to modify it though to 'unlink' the file on this XP computer. I couldn't 'unlink' as long as the connection was open.

<?php // demo/temp_tonelm54.php

/**
 * Use SQLite3
 *
 * Why? http://www.sqlite.org/mostdeployed.html
 *
 * http://www.sqlite.org/about.html
 * http://php.net/manual/en/book.sqlite3.php
 * http://php.net/manual/en/sqlite3stmt.bindvalue.php#116523
 */
error_reporting(E_ALL);
echo '<pre>';


// THE FILE PATH AND NAME OF THE DATABASE
$db_name = 'RAYSqLiteDate.db';

// SET UP OUR DATABASE USING SQLITE3
try
{
    $sql3_db = new SQLite3($db_name);
}
// NOTE - THE CONSTRUCTOR MAY THROW AN EXCEPTION
catch(Exception $exc)
{
    var_dump($exc);
    trigger_error('SQLite3 Fail: ' . $exc->getMessage(), E_USER_ERROR);
}


// A QUERY TO CREATE A TABLE
$sql
=
"
CREATE TABLE stock
( symbol TEXT
, price  REAL
, xtime  INTEGER
, key    INTEGER PRIMARY KEY AUTOINCREMENT
)
"
;

if (!$sql3_dbs = $sql3_db->query($sql))
{
    trigger_error('SQLite3 Failure', E_USER_ERROR);
}


// A QUERY TO LOAD THE TABLE
$sql
=
"
INSERT INTO stock
(  symbol,  price,  xtime ) VALUES
( :symbol, :price, :xtime )
"
;

if (!$sql3_stmt = $sql3_db->prepare($sql))
{
    trigger_error('SQLite3 Failure', E_USER_ERROR);
}


// SOME TEST DATA FOR OUR STOCK TABLE
$names = array
( array( 'symbol' => 'YHOO', 'price' =>   41.07, 'xtime' => strtotime('Yesterday') )
, array( 'symbol' => 'GOOG', 'price' => 1148.62, 'xtime' => strtotime('Yesterday') )
, array( 'symbol' => 'AAPL', 'price' =>  557.36, 'xtime' => strtotime('Today') )
, array( 'symbol' => 'JCP',  'price' =>    6.92, 'xtime' => strtotime('Tomorrow') )
)
;
// LOADING OUR ARRAYS OF TEST DATA INTO THE TABLE
foreach ($names as $name)
{
    $sql3_stmt->bindValue(':symbol', $name['symbol']);
    $sql3_stmt->bindValue(':price' , $name['price']);
    $sql3_stmt->bindValue(':xtime' , $name['xtime']);
    $sql3_stmt->execute();

    // GET THE SQLITE AUTOINCREMENT ID OF THE RECORD JUST INSERTED
    $rowid  = $sql3_db->lastInsertRowId();
    echo "SQLite3 INSERTED A ROW CONTAINING <b>" . $name['symbol'] . ' ' . $name['price'] . ' ' . $name['xtime'] . "</b> WITH AUTO_INCREMENT ID = $rowid" . PHP_EOL;
}


// A QUERY TO RETRIEVE INFORMATION FROM THE TABLE
$sql
=
"
SELECT rowid, symbol, price, xtime FROM stock WHERE price > :value ORDER BY xtime DESC LIMIT 3
"
;
echo PHP_EOL . $sql;

// PREPARES THE QUERY
$sql3_stmt = $sql3_db->prepare($sql);
$sql3_stmt->bindValue(':value', 500);
if (!$sql3_res = $sql3_stmt->execute())
{
    trigger_error('SQLite3 Failure', E_USER_ERROR);
}

// RETRIEVE THE RESULTS IN THE FORM OF ASSOCIATIVE ARRAY
while ($row = $sql3_res->fetchArray(SQLITE3_ASSOC))
{
    print_r($row);
}
// CLOSE THE RESULTS SET
$sql3_res->finalize();


// A QUERY TO REMOVE INFORMATION FROM THE TABLE
$sql
=
"
DELETE FROM stock WHERE xtime < :value
"
;
// PREPARE AND RUN THE QUERY
$sql3_stmt = $sql3_db->prepare($sql);
$sql3_stmt->bindValue(':value', strtotime('Today'));
$sql3_stmt->execute();

// SHOW THE NUMBER OF AFFECTED ROWS
echo "QUERY: $sql DELETED " . $sql3_db->changes() . " ROWS";
echo PHP_EOL;


// A QUERY TO SHOW THE INFORMATION LEFT IN THE TABLE
$sql
=
"
SELECT rowid, symbol, price, xtime FROM stock ORDER BY symbol
"
;
echo PHP_EOL . $sql;

// PREPARES THE QUERY
$sql3_stmt = $sql3_db->prepare($sql);

if (!$sql3_res = $sql3_stmt->execute())
{
    trigger_error('SQLite3 Failure', E_USER_ERROR);
}


// RETRIEVE THE RESULTS IN THE FORM OF ASSOCIATIVE ARRAY
while ($row = $sql3_res->fetchArray(SQLITE3_ASSOC))
{
    // CONVERT THE DATE TO ISO STANDARD
    $row['xtime'] = date('Y-m-d H:i:s', $row['xtime']);
    print_r($row);
}
// CLOSE THE RESULTS SET
$sql3_res->finalize();

// CLOSE THE connection
$sql3_db->close();

// REMOVE THE DATABASE
unlink($db_name);

Open in new window