The app I am currently developing (in Java, not the Ruby tutorial I’ve been talking about) relies on a timestamp to report only the most recent activity to it’s clients. So, why was it not properly reporting our test data?
After some deliberation I ran the simple statement:
select distinct trim(current_timestamp) from xxx.yyy
Why the ‘trim’? Well because for some reason WinSQL (on my machine) won’t properly retrieve a dataset containing a timestamp for me otherwise (but that’s another story).
Anyway, at 10:46AM it reports that it is currently 2:46PM. So, simple timezone issue, right? Well… maybe.
select dbtimezone from xxx.yyy
This gives me a ‘-6’. So, it does seem to know where we are.
Maybe it’s another WinSQL timestamp issue? Nope, I hack my app to report the timestamp to me – also seems to think it’s 2 this afternoon.
I have my buddy copy/paste my timestamp retrieval (against the same db): he gets 10:46AM! Ok, now this is quickly becoming a pain.
Checking the internet… Some rants are close but not much out there.
After much effort I get a statement mocked up to tell me what it looks like out on the development server. How long does it take for the “automated” deployment tool to get everything out on the dev server and restarted for me to do this? Why, a meer 15 minutes – no, I am serious; I literally timed it with my watch 14:47. Anyway, it comes up with the same (incorrect) stamp that I have locally!
After some effort I’ve found that systimestamp (as opposed to current_timestamp) will report consistenly for me. According to the documentation this is the same as saying “TIMESTAMP WITH TIME ZONE” – but now I don’t even have to worry about what the proper timezone is. Not that the server’s going to be crossing any statelines in the near future, but hey: it works!
The big mystery though is why the timestamp is applied to current_timestamp on some machines (seemingly) by default, while on others: it is not. Softaware? Configuration? Who will solve this mystery? Not me! It works and it’s 5PM on Friday!