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
tonelm54Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
0
Dave BaldwinFixer of ProblemsCommented:
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.
0
Ray PaseurCommented:
See if this makes sense.  I have not given this a lot of "best practices" thought in SQLite, mostly because PHP and MySQL play together so well in date/time processing and that's what I usually use.  That said, I think if you had a database abstraction layer you could automate some of the date/time conversion and arithmetic in the database class.
http://iconoun.com/demo/temp_tonelm54.php
<?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 = 'SqLite.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();


// REMOVE THE DATABASE
unlink($db_name);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave BaldwinFixer of ProblemsCommented:
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

1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.