How to handle UTC?

  • Hi,

    I'm trying to figure out a couple of things regarding UTC datetime on SQL 2005. The setup is a typical 3-tier architecture.

    Right now, I have a database server that stores datetime values using whatever the client application passes in. I want to be able to use UTC datetime in the database.

    However this poses challenges regarding the way to handle the following:

    1- Execute queries that retrieve datetime values

    2- Pass in datetime values from client to server

    3- Use datetime values passed from client to server for querying parameters

    I am wondering how people are approaching this. Are you using UTC as soon as the client boundaries are crossed (ie only converting the UTC data to local data for display purposes but in the background keeping everything UTC even on client side)? Or are you doing this in your queries by converting whatever the client has passed in using time zone information?

    I know this basically poses the challenge of picking where the conversion needs to occur and this is more of an architecture question but I'm just curious how people who have the same kind of setup go about this issue?

    Many thanks,

    Greg

  • The only way I have done this is to consider the conversion a client-side operation. Everything passed to the server, stored on the server, and used by the server was in UTC and the client applications converted for display purposes.

    It worked very well the two times I had to do this.

  • That's what I'm thinking too. It's more work on the client side but it helps spreading the load of converting the data and it also makes the server completely independent of this. My only concern is for datasets and grids that display datetime fields. The components we use are not very good for handling this without adding a lot of overhead in the processing of a dataset for display.

    Thanks,

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply