April 8, 2009 at 10:22 am
I have an application which writes records into 3 tables in my database each time it processes an event. This is a 3rd party app so I cannot change the structure of those tables, or the data that is written into those tables for each event (if I could, this probably would not be an issue!).
I have created a view which joins several columns from the 3 tables. Users are able to select from this view via their reporting application to work with the data.
In each of the 3 tables, there is a Time column. Each field is an integer representation of UTC time in seconds since 01/01/1970, so for example the field may contain "1239207273".
In my view, I have converted this field into separate date and time columns, like so:
select convert(varchar,dateadd(second, TIME, '19700101'),103) as Date,
convert(varchar,dateadd(second, TIME, '19700101'),108) as Time
from MyTable1
For the example above, this gives the Time field "16:14:33".
The problem being that this does not take local timezones into account. It is now GMT + 01:00 here in the UK so I need the correct time to be displayed. I can correct the time with the following conversion:
select convert(varchar, dateAdd(hour, datediff(hour,getutcdate(), getdate()), dateadd(second, TIME, '19700101')),108)
from MyTable1
This changes the Time to "17:14:33"
But of course, I do not want to simply convert all the Time values in the table into the current timezone, because some the data was entered when it actually was GMT.
The question is: What's the best way of getting the correct times into the view, depending on whether the value lies in GMT or BST (GMT + 01:00)?
I guess I need to say "if the date and time are within a certain range, perform the conversion to BST, otherwise take it as it stands", but it is possible to do this on the fly in a view??
April 15, 2009 at 7:21 am
I'm not sure if bumping posts is frowned upon here; if so then apologies, the topic flew off the front page pretty quick so I thought maybe it got missed.
Anybody have any thoughts?
Thanks
April 20, 2009 at 10:23 am
I've done something similar too hope you enjoy reading it.
http://www.sqlservercentral.com/scripts/GMT+conversion/66492/
April 20, 2009 at 9:48 pm
@adam-2 - how do you know which rows have a [Time] value that was entered in BST vs which ones were entered in GMT? Is there some other column that indicates this difference?
Date conversion is usually best left to the client consuming the data - what if some of your clients were in other timezones than the DB server?
If you really want to do it in a view you could use a CASE statement with your DATEADD() functions to conditionally add a different number of hours to each [Time] value depending on whether the original value was entered as BST or GMT. But that assumes you can tell the difference between those in the first place...
Regards,
Jacob
April 21, 2009 at 9:40 am
Fortunately the only timezone conversion required will be between GMT and BST.
There is also a Date field which obviously provides me with a reference to know when the conversion should be done and the CASE statement idea solves the problem perfectly. I had never even tried using a CASE statement in SQL until now 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply