Handling Dates in Open Data with JavaScript and Python

13 Jan

Have you ever opened public data and been greeted by a date field with a value like 1419490800000? If it is not obvious to you that the number represents December 25, 2014 then you are not alone. The date has been returned to you in milliseconds since January 1, 1970 at 00:00:00. It might be difficult for you and me to do the math and get a human readable value but for the computer it is a trivial operation. In this article, I will show you how to manipulate a date in milliseconds using data from cabq.gov.   Albuquerque has a growing collection of open data and one newer data sets is Crime Incidents. For this example. let’s grab the REST endpoint. Scrolling to the bottom of the page and clicking the supported operation link QUERY, we can look at the data before coding against it. In the form, set the where field to 1=1, the out fields to * and click the Query (GET) button. It will take a few minutes, but I received 24,629 records (this data set returns incidents for the last 180 days).

Sample of my query results

Sample of my query results

As you can see, the date field is in milliseconds. We will move on to writing a webpage to grab the data and return the date in a variety of other formats – which will be more useful for building our applications.

JavaScript Date

There are many date libraries available in JavaScript but for this example we will only use the built-in date object. The first thing we need to do is grab some data. I will start by using a query to grab a subset of incidents from 1/9/2015 until today. While the data is returned in milliseconds, we can query it using a human readable date format. Below, I have created a variable params by concatenating several strings. The strings use the escape() function because browsers tend to garble URLs with special characters and the function allows us to pre-encode them so we know what the conversion of the characters will be.

q1=’where=’; q2=escape(‘”date”>date’); q3=escape(” ‘2015-01-09 00:00:00′”) ; q4=”&outfields=*&f=json”; var params=q1.concat(q2,q3,q4);

Now that we have the parameter to pass, the rest is a standard AJAX request to the URL of the service.

var url = “http://coagisweb.cabq.gov/…/query “; http=new XMLHttpRequest(); http.open(“POST”, url, true); http.setRequestHeader(“Content-type”, “application/x-www-form-urlencoded”); http.onreadystatechange = function() { if(http.readyState == 4 && http.status == 200) { //Code goes here }} http.send(params);

Now we have queried the data and have an if statement that will run when the server hands us our results. The first thing to do is grab the response and convert it to JSON. Then we will grab the date from the first result and display it in the console.

theGeom= JSON.parse(http.responseText); adate=new Date(theGeom.features[0].attributes.date); console.log(“RESPONSE FROM ESRI: ” + theGeom.features[0].attributes.date);

If we were successful, you should see a millisecond date in the console. Using the date object, you can convert milliseconds to any of the below formats. Note that months are indexed starting at 0. That means January is month 0 and December is month 11. You need to add 1 to a month for it to be correct.

console.log(“TIME STRING: ” + adate.toTimeString()); //0=january 11 = december console.log(“MONTH: ” + adate.getMonth()+1); console.log(“DAY: ” + adate.getDay()); console.log(“YEAR: ” + adate.getFullYear()); console.log(“HOURS: ” + adate.getHours()); console.log(“MINUTES: ” + adate.getMinutes()); console.log(“SECONDS: ” + adate.getSeconds()); console.log(“ISO 8601: ” + adate.toISOString()); console.log(“UTC: ” + adate.toUTCString()); console.log(“STRING: ” + adate.toString()); console.log(“LOCALE: ” + adate.toLocaleDateString());

Results in the console

Results in the console

Using Python

Using Python, we can do exactly the same thing as the JavaScript example, but this time we will run the code of our desktop. We will need to import the urllibs and datetime. I have chosen to use a third party library simplejson.

import urllib, urllib2, datetime, simplejson

To query a REST endpoint in Python, you will:

  1. create a parameters object
  2. concatenate it to a url
  3. then open the url and read the reply.

param = {‘where’:’1=1′,’outFields’:’*’,’f’:’json’} url = ‘http://coagisweb.cabq.gov/…/MapServer/0/query? ‘ + urllib.urlencode(param) rawreply = urllib2.urlopen(url).read()

If you print rawreply, you will see a large string. You will need to convert the string to JSON. This is where I used simplejson.

reply = simplejson.loads(rawreply) print reply[“features”][0][“attributes”][“date”] print reply[“features”][0][“attributes”][“CVINC_TYPE”]

Your program should have printed out the millisecond date for the first result and the incident type. Now, we can assign the date to a variable and convert it to a readable format.

d=reply[“features”][0][“attributes”][“date”] readable = datetime.datetime.fromtimestamp(d/1000) print readable

The above code should print out something like: 2014-07-14 18:00:00. Now that you have the readable date, you can extract and convert it.

print “YEAR: ” + str(readable.year) print “MONTH: “+ str(readable.month) print “DAY: “+ str(readable.day) print “HOUR: ” + str(readable.hour) print “MINUTE: ” + str(readable.minute) print “SECOND: ” + str(readable.second) print “ISO: ” + str(readable.isoformat())

When all the code is executed, you will see the image below.

Console output of python code

Console output of python code

There you have it, converting milliseconds to a human readable date using two different languages.


One Response to “Handling Dates in Open Data with JavaScript and Python”


  1. Query Albuquerque Open Data with Turf.js | Architecture and Planning - February 9, 2015

    […] have posted on the Albuquerque Crime Incident data showing how to use Python and for converting the Date field. I have also demostrated how to use Turf.js for analysis. In this post, I will show you how […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: