Working with dates and SQLite in PhoneGap

This post is more than 2 years old.

One of my favorite features of Adobe AIR, and in HTML5, is the ability to make use of a local SQLite database. For mobile or desktop applications, having a little mini-database is incredibly useful. While SQLite can't replace SQL Server, as a single user embedded database it's a great feature to have. One thing that can be difficult though is dealing with types in SQLite. SQLite only has a few basic types, one of which is not a native date type. It does support date style functions and can do basic parsing. Getting dates working correctly in your PhoneGap application can be a bit tricky. Here's what I came up - and as always - if folks have a better solution, please share!

First off, I recommend quickly reading over the SQLite docs on types. It talks about the core types they support and how other types are mapped. While you can use a DATE type for your column, it's going to map to NUMERIC. I setup my JavaScript code to prepare a simple table.

function init() { document.addEventListener("deviceready", deviceready, true); }

var db;

function deviceready() { db = window.openDatabase("test", "1.0", "test", 1000000); db.transaction(setup, errorHandler, dbReady); }

function setup(tx) { tx.executeSql('create table if not exists log(id INTEGER PRIMARY KEY AUTOINCREMENT, '+ 'log TEXT, created DATE)'); }

Hopefully this makes sense. The important part is the table definition. To be clear, yes, I'm using DATE as a column type, but it's going to map to numeric in SQLite.

So given this, I decided I'd add some simple logic to make it very quick to add test data as well as list out my current data. So my HTML looks like so:

<!DOCTYPE HTML> <html> <head>

<meta name="viewport" content="width=320; user-scalable=no" /> <meta http-equiv="Content-type" content="text/html; charset=utf-8"> <title>PhoneGap</title> <script type="text/javascript" charset="utf-8" src="js/phonegap-1.2.0.js"></script> <script type="text/javascript" charset="utf-8" src="js/jquery-1.7.min.js"></script> <script type="text/javascript" charset="utf-8" src="js/main.js"></script>

</head>

<body onload="init()">

<input type="button" id="addButton" value="Click to Add">

<input type="button" id="testButton" value="Test Data">

<div id="result"></div>

</body> </html>

Not very realistic, but simple. One button to add stuff, one to list stuff out. Let's first take a look at the addButton click logic.

$("#addButton").on("touchstart", function(e) { db.transaction(function(tx) { var msg = "Log it..."; var d = new Date(); d.setDate(d.getDate() - randRange(1,30)); tx.executeSql("insert into log(log,created) values(?,?)",[msg,d.getTime()]); }, errorHandler, function() { alert('added row'); }); });

Since this is just a proof of concept, the code here isn't working with user-created data, but rather using a static string and a random date. (The function randRange is defined later in the file.) This function lets me click a few times to enter some random data. The dates will fall in a range of one day ago to 30 days ago.

The critical part here is how I store the dates. I tried storing them as is - but what happens is that the date is converted into a string. SQLite let's you define column types, but you can stick anything you want in them. I switched to getTime(), which returns the number of milliseconds since 1970. This will be a nice numeric value that will be easier to work with.

Ok, so now let's look at the display logic.

$("#testButton").on("touchstart", function(e) { db.transaction(function(tx) { tx.executeSql("select * from log order by created desc",[], gotLog,errorHandler); },errorHandler, function() {}); });

function gotLog(tx, results) { if(results.rows.length == 0) { alert("No data."); return false; }

var s = "";
for(var i=0; i&lt;results.rows.length; i++) {
	var d = new Date();
	d.setTime(results.rows.item(i).created);
	s += d.toDateString() + " "+d.toTimeString() + " (original="+results.rows.item(i).created+")&lt;br/&gt;";
}
$("#result").html(s);

}

The first block is the click handler for the button. It handles running my simple SQL. Note the order by clause. Since we are storing are dates as numbers, this is all we need to do - treat it like a number. How would you handle a date range search? For example, just the last week? Simple - make a JavaScript date object for today. One for 7 days ago. And then convert both to numbers using getTime().

The display logic is simple - if a bit ugly. I create new JavaScript date objects from the numbers stored in the database. I render that - and the original - just so I can see how things looked. Here's a screen shot:

Over all - pretty simple once you realize how you have to store the data and work with it on the client side. I've attached a zip of the entire project for folks who want to download and try it yourself.

Download attached file.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Phillip Senn posted on 1/7/2012 at 10:24 PM

There's just so much to JavaScript - like all these date functions. Another thing is that it's weird how db.transaction has an error callback followed by a success callback, while executeSql has a success followed by error.
So many examples on the net use anonymous functions, you've made it more clear by using named functions.

Comment 2 by Raymond Camden posted on 1/7/2012 at 10:37 PM

re: date functions
Well, it doesn't seem like a lot of code to me, but obviously, that's just my opinion

re:event handler order
I agree completely. Not quite sure why PhoneGap made that decision. I'm guessing just a mistake.

re:named handlers
I go back and forth. I'm still figuring out the best way to write db-intensive PG apps.

Comment 3 by Phillip Senn posted on 1/8/2012 at 1:05 AM

I've tried using jQuery.Deferred() with resolve and reject here:
http://stackoverflow.com/qu...

Someone mentioned the Step framework, which looks promising
http://stackoverflow.com/qu...

Comment 4 by Raj posted on 6/7/2014 at 10:44 PM

Hi

I have a small problem with WebSql /IndexedDB.

In My database, i have latitude and longitude.

I want to display all the stations near by with in 10 miles .

I am using WebSql.

Can you please provide me any solution ?

Thanks
R

Comment 5 by Raymond Camden posted on 6/7/2014 at 10:49 PM

There is a standard way of doing this with SQL - just Google. Whether it will work in SQLite is another story of course. You would need to test.

Comment 6 by Raj posted on 6/7/2014 at 10:52 PM

Hi Friend,

It is not working, i don't think trigonometric functions are available for sq-lite.

Do you have any suggestion for me?

i have search in google but unable to find the solution.

Thanks
R

Comment 7 by Raymond Camden posted on 6/7/2014 at 10:56 PM

What about the suggestion here: http://stackoverflow.com/qu...

Comment 8 by Raj posted on 6/7/2014 at 11:08 PM

I Am using WebSql,

I am developing an mobile aplication using Jquery mobile.

Wil this work with this?

Sorry for asking such questions, I am new to this area

Comment 9 by Raymond Camden posted on 6/8/2014 at 12:15 AM

Are you asking about what I linked to? I don't know - it was a suggestion. :)

Comment 10 by dina salah posted on 7/7/2014 at 7:21 PM

Hello,
I got log error
(23) Not authorized

In which cases I got this error?

Comment 11 by Raymond Camden posted on 7/7/2014 at 7:28 PM

@dina: Show me where I can run this online to test and I'll try to help.