Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SqlTimestampConverter Timezone UTC #45

Closed
Maze-fr opened this issue Mar 22, 2016 · 2 comments

Comments

@Maze-fr
Copy link

commented Mar 22, 2016

Hello,

I got a problem with the fact that SqlTimestampConverter forces its time zone to UTC.
It makes me loose my time zone specificity, so the hour I got is not the good one.

As I use XStream to send my objects to M$ SqlServer, it inserts / updates the time with what I sent, so not the good one...
I tried with ISO8601SqlTimestampConverter, but M$ SqlServer is unable to parse the string to timestamp, so the stored procedure fails.

I had to extends SqlTimestampConverter so I could override "format" field (using reflection) with the good time zone value : "getDefault()".

It would be very convenient to be able to change the time zone with a specific constructor with the time zone as parameter.

Greetings.

@joehni

This comment has been minimized.

Copy link
Member

commented Mar 25, 2016

What do you mean by "use XStream to send my objects to M$ SqlServer"? Did you simply use XStream's converters? What type has the row in MS SQL?

Actually UTC is on purpose. If you persist a timestamp in XML, you will expect to get the same timestamp back, independently if it is deserialized with different daylight saving or even in another timezone.

@Maze-fr

This comment has been minimized.

Copy link
Author

commented Mar 25, 2016

I have M$ SqlServer 2005 as database, and in order to have a simple way to manage updates and inserts, I use XStream to convert my Java objects into XML that I send to my stored procedure for use with syntax "OPENXML(some parameters) WITH My_Table". With that syntax, the mapping is done between my XML and My_Table columns automatically.

The problematic DB column type is "datetime", so I use "java.sql.Timestamp" to get it.
When I user "ISO8601SqlTimestampConverter", the DB don't manage to convert it to "datetime".
When I user "SqlTimestampConverter", UTC time is sent and my DB put it in a non UTC "datetime" (as it looks like) column, so I loose my time zone specification.

With 2008 version or early, I would use "datetimeoffset" column type and I wouldn't have any problem, but... well... bad luck, it is 4 years they are saying they want to upgrade to 2012 version, and now they started everything back saying they want to upgrade to 2014 version...

I thought about dealing with it in the stored procedure, but I have data transfers between different schemas through Java in XML strings (I know, it sucks ; don't ask why...) and "datetimes" don't need to be modified in that case. So I must generate XML with time zoned "timestamps" to keep it working properly.

I understand that UTC is on purpose, and I really think it's the good choice, as default.
But we should have the choice for those who work in a client environment where other people don't know how (or want) to do things well, and you have to deal with it...

@joehni joehni added the enhancement label Mar 29, 2016
@joehni joehni added this to the 1.4.x milestone Mar 29, 2016
@joehni joehni self-assigned this Mar 29, 2016
@joehni joehni closed this in 57c01e8 Nov 9, 2016
joehni added a commit that referenced this issue Nov 9, 2016
@joehni joehni modified the milestones: 1.4.x, 1.4.10 Jun 9, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.