JSP and PostgreSQL Time Stamp Mayhem 17th May 2006

So today has been quite a strainer, as part of a project I’m working on we generate PDF documents using JSP and PostgreSQL. The data is made up from various SQL queries depending on what we want to appear in the report. A huge brickwall I ran into today was that the creation dates for the reports are stored as 13 digit UNIX timestamps and PostgreSQL uses 10 digit timestamps.

We create the initial dates using the Calendar class:

import java.util.Calendar;
 ...
creationDate = Calendar.getInstance().getTimeInMillis();

then later on when we pull the data back out again we do:

import java.util.Calendar;
...
Calendar cal = Calendar.getInstance();
cal.setTimeInMillis(input);
SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yy");
String niceDate = formatter.format(cal.getTime());

Which gives us a nicely formated date we can output onto the page. The problem is just before that last segment when we try to perform our SQL query. The way it works is that inside SQL we have to check a single part of the date item as part of our lookup, in this case we only wanted records that had been created this year. This is where the problem started. We’ve been storing the creation date with a BIGINT column type which in itself isnt that big a deal but the fact that the dates have 13 digits is. PostgreSQLs timestamp and timestamptz functions won’t give you the date that your expecting. I kept getting told the reports were made in the year 38932 which I was pretty sure they weren’t.

I couldn’t work out the problem, it was a real corker. You see I hadn’t actually realised the length difference when I first saw the problem dates so I spent the half of the day trying every combination of everything I could find. Nothing seemed to work. Then as the afternoon started up I decided to stick the different date strings into unix time stamp.com. That’s when my oversight first appeared to me. We had been storing the dates down to the millisecond, but PostgreSQL only sees timestamps down to the second. Instantly the answer was in front of me, all we had to do was divide the milliseconds down to seconds which will then give us the date we expect:

select timestamptz 'epoch' + tbl_reports.creation_date * 0.001 * interval '1 second' from tbl_reports

Well that wasnt exactly how I used it but that shows how to get your 13 digit BIGINT date into a PostgreSQL compatible timestamp.

So you might be saying “how come you stored the dates differently in the firstplace?”, I wondered that myself so I asked why they did that and they said it was because they normally do all their date formating with JSP so it just made sense to store the dates in the format it uses.

So all in all today was pretty kickass since I learnt tons about the various date objects and their many functions for both JSP and PostgreSQL. Best of all I managed to get that “Yay I solved it” vibe just before the end of the day ?

The funny thing was that if I’d looked at any of the actual creating the creation date areas in the project I would of seen the function getTimeInMillis() being used whose title kinda gives the time difference thing away, saw it when I was copying and pasting the code into here and definitely had a ..doh!.. moment ?