Its time or what?

So I started working for real. I got offered a full-time job where I was employed as a student-worker. So I've gone full-time working and left the studies for a year. So currently I'm working for Zylinc A/S, danish company in IP-Telephony making switch-boards.

So today while I was working, there was some problems with time formatting in my project. The project is a common data access layer for a bunch of Java portals. And since my main focus is statistics I work with dates and time. All date's and times in this post is in 24hr and date/time format is as following YYYY-MM-DD HH-mm-ss[1]

First things first
I found out that MySQL handles date/time strangely, compared to the SQL standard[2]. According to the standard TIMESTAMP is a type containing date and time information in a format like "2010-09-21 22:21:20". This seems obvious, furthermore two types are definied, DATE which consists and contains only the date part(2010-09-21) of a TIMESTAMP value, and the second being TIME containing only the time component(22:21:20).

All this is fine and looks good. But what about the real world? Thats where MySQL comes in. Imagine you have a stored procedure, let's call it foo it takes two parameters(eg two sets of date and time) and returns the recorded statistics in the interval. So based on our knowledge from before we write a procedure like this:

Delimiter $$
DROP PROCEDURE IF EXISTS `foo`$$

CREATE PROCEDURE `foo`(
    param1 TIMESTAMP,
    param2 TIMESTAMP)
BEGIN
    SELECT BeginTime, EndTime, ... FROM Statistics
    WHERE BeginTime >= param1 AND BegindTime <= param2;
END PROCEDURE$$

This seems like the correct way to do it, but not surprisingly it isn't. See this discussion: http://stackoverflow.com/questions/409286/datetime-vs-timestamp. So this means my procedure definitions should look like this instead:

Delimiter $$
DROP PROCEDURE IF EXISTS `foo`$$

CREATE PROCEDURE `foo`(
    param1 DATETIME,
    param2 DATETIME)
BEGIN
    SELECT BeginTime, EndTime, ... FROM Statistics
    WHERE BeginTime >= param1 AND BegindTime <= param2;
END PROCEDURE$$

All right, so this works for MySQL, does it work for MSSQL to? So it seems(I haven't studied this further, since it isn't something I'm going to use - but soon as I need it, I will write about it).

Enough about types, what does a DATETIME value accept? I've found several ways of interacting with it
For example you can pass in this value:
"2010-09-21" - goes fine, a warning should be raised
"2010-09-21 22:21:20" - expected, all goes well
Theese above values seem logic, but then comes:
NOW()-0(=20100921222120.000000) - Strangest value I ever seen.

What does this mean? Can I use a value like 1285107680(Epoch time for the example date) or would that mean: 1285-10-76 80:00:00 or 0000-12-85 10:76:80 or how? I'm seriously confused about this. Not that I found a real solution to this problem, I'm now just aware of this behavior. As I wish you now are and will take this into consideration when developing with MySQL.

Second comes java
First it is important to know what the Epoch is.What I'm talking about is the UNIX Epoch, better known as seconds since 1970-01-01 00:00:00 UTC. Everybody's talking about it, it is the universal time representation for all sorts of things, even windows developers uses this format. But in the Java world, seconds is not good enough, understandable somehow.

Drawing on the good old examples milliseconds can be quite important doing stock trading but come on, at least make it compatible with the rest of the world, an approach where integer values are treated as UNIX Time and the decimal part(if using a float value) as milliseconds, and if you like, you could have nanoseconds(hows that for your stock trading needs?). I spent half a day figuring this out, looking at output from stupid tests being confused. Documentation should state this more clearly[3].

So thats almost everything from an exciting day at work - unless some extremely interesting stuff comes by, I plan on writing a little jump start tutorial for MyBatis[4] tomorrow, I found some interesting things they didn't put in the manual. Btw: I also did a contribution to Stripes web framework[5], I hope that when getting a little deeper into MyBatis I can make some contributions there to.

Have a great evening - and happy coding out there.

[1] = See this for reference http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html
[2] = ANSI SQL http://en.wikipedia.org/wiki/SQL#Date_and_time
[3] = So now I've done it, hope google catches this.
[4] = http://mybatis.org/
[5] = http://www.stripesframework.org