How to format a timestamp in the Excel data set?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

How to format a timestamp in the Excel data set?

Marcel Stör
Something in my Excel -> DBUnit -> H2 setup is driving me nuts.

The timestamp value I enter into my Excel cell is not what is finally
loaded from H2. At the moment I'm pretty clueless as for who the culprit
is. Here are few examples, right is what is in Excel left is the value
in Java:

Excel cell format text
------------------------------
2012-01-01                    -> 2012-01-01T01:00:00.000+01:00
2012-01-01T01:00:00.000+01:00 -> 2012-01-01T01:00:00.000+01:00
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00

custom cell format (timestamp)
------------------------------
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00
(JJJJ-MM-TT hh:mm:ss.fffZ)
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00
(JJJJ-MM-TTThh:mm:ss.fffZ)
2012-01-01 06:45:15           -> 2012-01-01T08:45:15.000+01:00
(JJJJ-MM-TT hh:mm:ss), 2 hours off

I find the last one particularly interesting...

The system i.e. JVM time zone is indeed +1 (user.timezone=Europe/Berlin).

Cheers,
Marcel

--
Marcel Stör, http://www.frightanic.com
Couchsurfing: http://www.couchsurfing.com/people/marcelstoer
O< ascii ribbon campaign - stop html mail - www.asciiribbon.org

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user
Reply | Threaded
Open this post in threaded view
|

Re: How to format a timestamp in the Excel data set?

John Hurst-2
Yuck. Timezone/locale issues. They mystify and depress me. I'm sorry I don't know the answers here. 

If you feel like posting a self-contained test case I could run it on my machine (Ubuntu, NZDT (+13:00)) to see if it is machine-specific.

John Hurst
Wellington, New Zealand

On Fri, Feb 3, 2012 at 10:27 AM, Marcel Stör <[hidden email]> wrote:
Something in my Excel -> DBUnit -> H2 setup is driving me nuts.

The timestamp value I enter into my Excel cell is not what is finally
loaded from H2. At the moment I'm pretty clueless as for who the culprit
is. Here are few examples, right is what is in Excel left is the value
in Java:

Excel cell format text
------------------------------
2012-01-01                    -> 2012-01-01T01:00:00.000+01:00
2012-01-01T01:00:00.000+01:00 -> 2012-01-01T01:00:00.000+01:00
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00

custom cell format (timestamp)
------------------------------
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00
(JJJJ-MM-TT hh:mm:ss.fffZ)
2012-01-01T06:45:15.000+01:00 -> 2012-01-01T01:00:00.000+01:00
(JJJJ-MM-TTThh:mm:ss.fffZ)
2012-01-01 06:45:15           -> 2012-01-01T08:45:15.000+01:00
(JJJJ-MM-TT hh:mm:ss), 2 hours off

I find the last one particularly interesting...

The system i.e. JVM time zone is indeed +1 (user.timezone=Europe/Berlin).

Cheers,
Marcel

--
Marcel Stör, http://www.frightanic.com
Couchsurfing: http://www.couchsurfing.com/people/marcelstoer
O< ascii ribbon campaign - stop html mail - www.asciiribbon.org

------------------------------------------------------------------------------
Keep Your Developer Skills Current with LearnDevNow!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-d2d
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user



--
Life is interfering with my game

------------------------------------------------------------------------------
Try before you buy = See our experts in action!
The most comprehensive online learning library for Microsoft developers
is just $99.99! Visual Studio, SharePoint, SQL - plus HTML5, CSS3, MVC3,
Metro Style Apps, more. Free future releases when you subscribe now!
http://p.sf.net/sfu/learndevnow-dev2
_______________________________________________
dbunit-user mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/dbunit-user