Friday, November 11, 2005

SQL TIMESTAMP headache

So, the truth comes out. Oracle and MySQL appear to not hand back TIMESTAMP as millisecond values. After numerous tests (all Java) in multiple timezones we've decided we're not going insane. The databases appear to not deliver TIMESTAMP as milliseconds, but as a String, always. Even from native console applications. There may be milliseconds that reside underneath, but they represent the String and cannot be retrieved. So, no, the TIMESTAMP going is not *always* normalized to UTC (could only get that to be the case when the db box was UTC and the JVM connecting via JDBC was also UTC). We tried multiple scenarios connecting in different timezones and continually found ourselves asking why the date appeared coming back differently in all places if it was really just stored in milliseconds. Perhaps it is, but it's not being given back that way. And the String is transformed by the JVM into the appropriate milliseconds to represent the toString (on the java.util.Date/java.sql.Timestamp) the JVM thinks it should show in your timezone.

At this point the question becomes, "So, how does this work with internationally replicated/distributed databases servicing multiple international JVMs?" It doesn't work, and after several days of testing, we're feeling confident enough to conclude this: the millis are (sometimes) being adjusted by the JDBC drivers to show you what it thinks you expect--so if you're really expecting UTC, then all the boxes' timezones should be GMT/UTC! That's sort of an issue if you plan on using servers in multiple places throughout the world to handle multiple web applications--which is exactly what we cannot assume.

So, the solution is simple:store the milliseconds as a long. Since Java (and most other sane languages) calculates date based upon the Unix Epoch, it makes sense. Furthermore, there's no guess work as to db timezone, connection timezone, driver millisecond manipulation--it's not a concern anymore.

When does the format of a date being storing in the database really matter? Depends on the need for precision, depends on the need of the application and it also depends on the requirement driving it in the first place. When the requirement dictates international interaction, the first thing that should come to mind is to store the date in a common time: GMT. Other consideration should be given to viewers and manipulators, what do they expect to see the time as? Locally or universal? If the answer for all of these questions point to an international need, the above solution is a simplistic and intuitive approach to resolving the design decision.

-------------begin Hibernate UserType-------------
package org.lds.sql;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Comparator;
import java.util.Date;
import java.util.TimeZone;
import net.sf.hibernate.Hibernate;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.UserType;
import org.lds.env.utils.DateUtils;


/**
* Custom Hibernate type for precise UTC millisecond persistance and retrieval using a Timestamp.
* All values represent the given time from the Unix Epoch, 1 Jan 1970 00:00:00.000
* See http://en.wikipedia.org/wiki/Unix_epoch for more info.
* Credit: Rob @ "http://www.hibernate.org/100.html
*
*/
public class UtcTimestamp extends Timestamp implements UserType {
/**
* Gets the value of SQL_TYPES
*
* @return the value of SQL_TYPES
*/
public static int[] getSQL_TYPES() {
return UtcTimestamp.SQL_TYPES;
}


/**
* SQL type.
*/
private static final int[] SQL_TYPES = { Types.BIGINT };

/**
* Creates a new UtcTimestamp instance at this moment.
*
*/
public UtcTimestamp(){
super(System.currentTimeMillis());
}

/**
* With the given milliseconds.
*
* @param utcMillis a long value
*/
public UtcTimestamp(long utcMillis){
super(utcMillis);
}

/**
* Make a copy of the Timestamp.
* @see UserType#deepCopy(java.lang.Object)
*/
public Object deepCopy(Object obj) throws HibernateException {
return (obj == null) ? null : new Timestamp(((Timestamp)obj).getTime());
}

/**
* Compare via {@link Object#equals(java.lang.Object)}.
* @see UserType#equals(java.lang.Object, java.lang.Object)
*/
public boolean equals(Object x, Object y) {
return (x == null) ? (y == null) : x.equals(y);
}

/**
* Timestamps are mutable.
* @see net.sf.hibernate.UserType#isMutable()
*/
public boolean isMutable() {
return true;
}

/**
* Return an instance of the Timestamp or null if no value is specified.
* @see net.sf.hibernate.UserType#nullSafeGet(java.sql.ResultSet, java.lang.String[], java.lang.Object)
*/
public Object nullSafeGet(ResultSet rs, String[] columns, Object owner)
throws HibernateException, SQLException {

long value = rs.getLong(columns[0]);
Timestamp timestamp;

if(rs.wasNull()) {
timestamp = null;
} else {
timestamp = new UtcTimestamp(value);
}
return timestamp;

}

/**
* Set an instance of the Timestamp into the database field.
* @see net.sf.hibernate.UserType#nullSafeSet(java.sql.PreparedStatement, java.lang.Object, int)
*/
public void nullSafeSet(PreparedStatement statement, Object value, int index)
throws HibernateException, SQLException {

if(value == null) {
statement.setNull(index, Types.BIGINT);
} else {
Timestamp timestamp = (Timestamp)value;
statement.setLong(index, timestamp.getTime());
}
}

/**
* Return the {@link Timestamp} class.
* @see net.sf.hibernate.UserType#returnedClass()
*/
public Class returnedClass() {
return org.lds.sql.UtcTimestamp.class;
}

/**
* Return the supported SQL types.
* @see net.sf.hibernate.UserType#sqlTypes()
*/
public int[] sqlTypes() {
return SQL_TYPES;
}

/**
* All dates return their value in GMT time.
*
* @return a String value
*/
public String toString(){
SimpleDateFormat dateFormat = new SimpleDateFormat(DateUtils.DATE_FORMAT);

dateFormat.setTimeZone(DateUtils.GMT);

return dateFormat.format(this);
}

/**
* Obtain a UtcTimestamp from a Date.
*
* @param date a java.util.Date value
* @return an UtcTimestamp value
*/
public static UtcTimestamp valueOf(java.util.Date date){
UtcTimestamp utcTimestamp = new UtcTimestamp(date.getTime());

return utcTimestamp;

}

}
-------------end Hibernate UserType-------------

External firewire dvd burner

I have a Toshiba SD-R5372 that I wanted to use to backup my system. Didn't take too much effort to get it working last night. I added SCSI CDROM support as a module and also 1394 SBP as a module (the other 1394 options I already had built-in to the kernel).

Emerged the latest k3b, reboot, modprobe sbp2 and modprobe sr_mod.
Plugged in the burner and watched /var/log/messages recognize it.
Added a device in k3b as /dev/sr0
Burned 4G in 20 minutes.

Once the burning was complete I mounted the drive as "mount /dev/sr0 /mnt/dvdrw" and verified the disc has my files!

Wednesday, November 09, 2005

Vonage, how easy

Number transfer took 20 days exactly. Called up AT&T and the phone service was shutoff today. Walked out side, opened up the panel, disconnect my wires (light blue and white blue for me). Walked in, plugged the phone line from the Vonage router into the wall. Picked up the phone plugged into the wall downstairs and have been calling out (and receiving) ever since. Sweet.

Secondly, Melissa has been talking to family in Idaho while I'm downloading two Sun apps @ 245KBps! Wow.

Further confirmation all dates are UTC millis, regardless of db timezone settings

(08:36:39) Russ: have a second?
(08:36:51) jamisbuck: sure
(08:36:57) Russ: do you use Mysql much?
(08:37:02) Russ: or Oracle?
(08:37:05) jamisbuck: mysql
(08:37:07) jamisbuck: I used oracle at byu
(08:37:17) Russ: great; done much with timestamps in mysql?
(08:37:22) jamisbuck: no, I avoid them
(08:37:26) jamisbuck: they don't act very intuitively
(08:37:36) Russ: intuitively...meaning?
(08:38:00) jamisbuck: updating the row automatically updates the timestamp, even if it was not explicitly requested
(08:38:03) jamisbuck: which goes counter to my way of thinking
(08:38:12) Russ: ah, right, only for the first column though ;)
(08:38:19) Russ: so, that aside
(08:38:45) Russ: do you explicitly set the timezone of your Mysql server to something other than the default SYSTEM?
(08:38:51) jamisbuck: no
(08:39:15) jamisbuck: we always store the times as utc, and explicitly transform them ourselves to the customer's time zone on display
(08:39:25) Russ: okay, that's exactly what I wanted to chat with you about
(08:39:30) Russ: the concept of storing them as UTC
(08:39:52) Russ: isn't *any* timestamp (or date/datetime for that matter) stored in UTC as millis from the epoch?
(08:40:26) jamisbuck: I have no idea :( but I'm pretty sure mysql dates/datetimes aren't limited to the epoch
(08:40:44) Russ: correct, they're not, the negative values work retroactively
(08:41:49) Russ: I'm struggling with storing time as UTC. Given a Calendar object in Java for today 9 Nov 00:00:00.000 GMT, I can store that in the database as my timestamp and verify the millis going in are the millis coming out--which is what I would expect if the date is indeed stored in UTC.
(08:42:07) Russ: The kicker is, any time I view that date via mysql console (or otherwise), the String representation is MST
(08:42:15) Russ: that is, 8 Nov 17:00:00:00.000
(08:42:30) Russ: not such a big deal, except that it can be confusing to convince that the date is correctly stored
(08:42:35) Russ: when not evaluating the millis
(08:42:35) jamisbuck: what is the timezone for your system?
(08:42:39) Russ: MST7MDT
(08:42:43) jamisbuck: yah, ours is utc
(08:42:52) Russ: I even changed that
(08:42:53) jamisbuck: I bet mysql is converting the date to the system's zone on output
(08:42:57) Russ: that's what I'm thinking
(08:43:10) Russ: I changed everything I could find, *except* my local timezone, and I get the same result
(08:43:20) Russ: even remote connections, from a different time zone
(08:43:36) Russ: which again points to the mysql server on my box "translating" the appropriate time
(08:43:49) jamisbuck: right
(08:43:50) Russ: so, theoretically, if I change my system timezone to UTC, I'll see the String I expect?
(08:44:01) jamisbuck: let me check something quick
(08:44:18) Russ: thanks for your input on this, I've been about on the verge of insanity of verification of this
(08:45:16) Russ: and the only remaining reason is that my system timezone is involved in the String representation
(08:45:31) jamisbuck: ok, I take it back, our servers have CST as the time zone, and mysql is configured to use SYSTEM as the time zone
(08:45:41) jamisbuck: but when I select a date via the mysql console, it doesn't show me any TZ information
(08:45:46) Russ: correct
(08:45:50) jamisbuck: 2004-04-11 07:07:37
(08:46:06) Russ: which if you get the millis, and view the UTC representation, will be the offset of CST
(08:46:22) jamisbuck: k, well, it has never bitten us in our rails apps
(08:46:29) jamisbuck: the times go in and come out correctly
(08:46:32) Russ: exactly
(08:46:41) Russ: as long as you use the millis, and not the string reported, that should be the case
(08:46:52) jamisbuck: to be honest, I use script/console more than I use mysql
(08:47:14) Russ: don't follow...you mean the console mysql executable?
(08:47:34) jamisbuck: I use my rails' app's script/console, istead of the mysql client program
(08:47:37) Russ: gotcha
(08:48:15) Russ: well thanks a bunch for your confirmation--my expectation is that whatever driver is managing the connection and result sets returns the millis any time a date/time/timestamp is retrieved
(08:48:37) Russ: the String representation is the secondary, because of the TZ offset calculations
(08:48:47) jamisbuck: alright, glad I could help
(08:48:48) Russ: which brings me around to, why set the timezone on a database?
(08:49:25) jamisbuck: I seem to remember that you can set some properties on the client connection. Maybe some of those specify how to deal with time zones
(08:49:38) jamisbuck: but it doesn't seem like it would help much if you are dealing with dates in multiple time zones
(08:49:44) Russ: exactly
(08:50:00) Russ: in Java you can muck with the millis going in if you provide the connection timezone
(08:50:18) Russ: and the JDBC driver will essentially "translate" the GMT millis to represent the timezone equivalent
(08:50:20) Russ: if that makes sense
(08:50:24) jamisbuck: yah
(08:50:29) Russ: anyhow, thanks again
(08:50:38) Russ: I wondered how Ruby dealt with it
(08:50:50) Russ: and it you've definitely confirmed my expectation
(08:50:52) Russ: have a good one!
(08:50:57) jamisbuck: you too!

GMT time and my headache

So say you care about a given date, like today, 9 Nov. It means so much to you that you want to store it in the database. Turns out if you're using Java, then what you're really storing is the GMT millis of the date and the database is storing that date in the same format. In other words, regardless of the time zone in which you reside, or where the database resides, 9 Nov GMT is 9 Nov GMT.

Regardless of my posts earlier about storing GMT dates in the database, I'm not so conviced that we *weren't* doing that in the first place. Fact is, using the Calendar in the PreparedStatement really only hoses with the millis being stored and my verification that "the right timestamp" was being stored was purely based on the String representation. In all actuality, if millis going in == millis coming out, then the date that is being stored at the moment indicated by the millis is very much indeed being stored in GTM.

But what about the settings for getting Oracle (alter database time_zone=xyz) or Mysql (mysqld_safe --default-time-zone=xyz)? I dunno what bearing that actually has. What I do know is that regardless of the time zone that Mysql is initiaiized with, the String representation of any date always appears as MST. For example, I have a date representing today (9 Nov 00:00:00.000 GMT). I persist this via JDBC and then immediately request back the value of the timestamp. Both sets of millis are identical. However, the date shown from toString() shows 8 Nov 17:00:00.000 since Java knows that JVM's timezone is MST7MDT. Likewise, if I access that same record and look at the date through the mysql command console, I see the same date!

What does this mean? I can explain the Java representation since it's using the timezone of my box (and Bryan and I tested it when he set his Apple to Tokyo time and we verified millis were correct, but String was appropriate for the Tokyo timezone), but I cannot explain the mysql console. Furthermore, why can't I just see millis? Something is translating those millis into MST7MDT. Still need to test this on Oracle. But, for the time being, I'm quite confused about the notion of storing timestamps in GMT when it appears that the Oracle/Mysql are doing exactly that anyway (as indicated by my tests, which I should post).

Tuesday, November 08, 2005

Oracle 9i GMT dates

Oracle made it too easy, here's the test code producing desired results:


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.TimeZone;
import java.sql.ResultSet;

public class TestOracleDate{

public static void main(String[] args){

SQLException exception = null;
Connection con = null;
String connectionString = "jdbc:oracle:thin:@testbox:imstest";
String user = "testuser";
String password = "testpassword";
String database = "testdb";
String uuid="c93d4654-40c2-11da";
String identifier="hoser date test";
String driverClassName="oracle.jdbc.driver.OracleDriver";

try{
Class.forName(driverClassName).newInstance();
}catch(Exception ex){ex.printStackTrace();}
try{
con = DriverManager.getConnection(connectionString,user,password);
String insertString = "INSERT INTO TEST_TABLE (cmn_rswd_identifier,cmn_rswd_statusid,cmn_rswd_mdate,cmn_rswd_cdate,cmn_rswd_uuid) VALUES ('"+identifier+"',1,?,?,'"+uuid+"')";
String deleteString = "DELETE FROM CMN_RESERVED_WORD where cmn_rswd_identifier='"+identifier+"'";
//insert = con.createStatement();
final PreparedStatement insert = con.prepareStatement(insertString);
final PreparedStatement delete = con.prepareStatement(deleteString);
System.out.println("executing: " + deleteString);
delete.executeUpdate();
System.out.println("executing: " + insertString);
TimeZone GMT = TimeZone.getTimeZone("GMT-00:00");
TimeZone MST = TimeZone.getTimeZone("MST7MDT");
System.out.println("First timestamp param, using :" + GMT.getID());
System.out.println("First timestamp param, using :" + MST.getID());
insert.setTimestamp(1,new Timestamp(System.currentTimeMillis()),Calendar.getInstance(GMT));
insert.setTimestamp(2,new Timestamp(System.currentTimeMillis()),Calendar.getInstance(MST));

insert.executeUpdate();

String queryString = "SELECT * FROM CMN_RESERVED_WORD where cmn_rswd_uuid='"+uuid+"'";
Statement query = con.createStatement();
ResultSet rs = query.executeQuery(queryString);
while(rs.next()){
//System.out.println(rs);
System.out.println("-----------------");
System.out.println("cmn_rswd_mdate (positional param 1) " + rs.getTimestamp("cmn_rswd_mdate"));
System.out.println("cmn_rswd_cdate (positional param 2) " + rs.getTimestamp("cmn_rswd_cdate"));
System.out.println("-----------------");
}

}
catch(SQLException ex){
ex.printStackTrace();
}
catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
finally{
try{
if(con != null) con.close();
}catch(Exception ex){ex.printStackTrace();}
}
}
}


Sources:
http://www.oracle.com/technology/products/oracle9i/htdocs/9iober2/obe9ir2/obe-nls/html/datetime/datetime.htm

http://www.dbasupport.com/oracle/ora9i/TimeZone.shtml

GMT dates for Mysql (MST7MDT) via JDBC, success

Finally able to store MST dates as GMT time in the database. Had to load the timezone tables first, /usr/local/mysql/bin/mysql_tzinfo_to_sql /usr/share/zoneinfo/ | /usr/local/mysql/bin/mysql -u root -p mysql. Here's the setup:

mysql 4.1.3-beta-standard

Connector/J:3.1.11-bin OR 3.2.0-alpha-bin

url:
jdbc:mysql://localhost:3306/unity?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=MST7MDT

mysql executable: /usr/local/mysql/bin/mysqld_safe --user=root --default-time-zone=MST7MDT &

Prepared statement (Hibernate)code:

public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException{
Timestamp timestamp;
if(value instanceof Timestamp){
timestamp = (Timestamp) value;
}
else{
timestamp = (Timestamp) new LocalizedTimestamp();
}

Calendar gmtCal = Calendar.getInstance(GMT);
//where GMT = TimeZone.getTimeZone("GMT-00:00");

st.setTimestamp(index, timestamp,gmtCal);
}


The catch is that this code will be called on all of our TIMESTAMP types, since we're overriding the existing Hibernate TIMESTAMP type (as LocalizedTimestamp). However, this could be easily resolved by the generated DAOs setting some flag on the LocalizedTimestamp object. As it is, some of us are of the mindset that all the timestamps we store should be GMT, so this still works.

-----------
Update
The alternative, producing the same result, is as follows:
/usr/local/mysql/bin/mysqld_safe --user=root --default-time-zone=UTC &

jdbc:mysql://localhost:3306/unity?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=UTC

st.setTimestamp(index, timestamp);

Monday, November 07, 2005

mysql 5 and localized dates

Problems with persisting timestamps in Mysql 5 via JDBC:

3.0.9
Cannot convert value '2004-08-16 12:00:00' from column 4(2004-08-16 12:00:00 ) to TIMESTAMP.

3.1.11
java.sql.SQLException: Invalid value for getInt() - '^A' in column 7

3.2.0 alpha
Caused by: java.sql.SQLException: Unknown type '16 in column 6 of 21 in binary-encoded result set.

with the following code:

st.setTimestamp(index, timestamp,gmtCal);

where st is a PreparedStatement and the gmtCal is a Calendar instance set at GMT.

Also, the URL connection string was:
jdbc:mysql://localhost:3306/unity?useUnicode=true&characterEncoding=utf8&useTimezone=true&serverTimezone=MST7MDT