[SOLVED] Using ADDDATE to convert from UTC

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

[SOLVED] Using ADDDATE to convert from UTC

Author

Topic

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-14 : 10:01:56

Let me start by admitting that I have been working with SQL for less than a week. I have been assigned this 'project' as a learning experience because my coworker who usually does all of our SQL programming is working off-site and isn't available.

That said, I am building an SQL database that logs data collected from analog instrumentation such as electrical current meters, temperature transmitters, etc.

I currently have two tables. The first table (dbo.PL_Analog) has 4 columns: Date_Time, TagName, Value, and Engineering Units. See example below.

Date_Time TagName Value EU
2010-12-11 16:08:46.337 CT_Monitoring.L1E.L1E_KW 0.448000013828278 NULL
2010-12-11 16:08:46.337 CT_Monitoring.L1M.L1M_KW 0.811999976634979 NULL
2010-12-11 16:08:46.337 CT_Monitoring.P1.P1_KW 1.95200002193451 NULL
2010-12-11 16:08:46.337 CT_Monitoring.R1MA.R1MA_KW 0 NULL
2010-12-11 16:08:46.337 CT_Monitoring.R1MB.R1MB_KW 12.576000213623 NULL

The Date_Time column contains a UTC date and time as a datetime type. The EU (Engineering Units) column is Null by default. The second table (dbo.ENG_Units) contains the unit for each unique TagName. For example, all of the devices above measure electrical current and therefore should include the units of kilowatts. See the example below.

TagName EU
CT_Monitoring.R1MB.R1MB_KW KW
CT_Monitoring.R1MA.R1MA_KW KW
CT_Monitoring.P1.P1_KW KW
CT_Monitoring.L1M.L1M_KW KW
CT_Monitoring.L1E.L1E_KW KW

I use the following query to populate the EU column and insert the updated data into a new table (dbo.CM_AnalogCopy).

INSERT INTO dbo.CM_AnalogCopy
(
Date_Time
, TagName
, Value
, EU
)

SELECT PL_Analog.Date_Time, PL_Analog.TagName, PL_Analog.Value, Eng_Units.EU
FROM PL_Analog
FULL JOIN Eng_Units
ON PL_Analog.TagName=Eng_Units.TagName
ORDER BY Date_Time

I would also like to create and populate a new column (Date_Time_Local) in dbo.CM_AnalogCopy that contains the LOCAL time, which is UTC-6. It appears that this can be accomplished using DATEADD(hour,-6,Date_Time), but I do not know how to incorporate this function into my existing query. Any help is greatly appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-14 : 10:27:12

looks like you got it figured out


SELECT PL_Analog.Date_Time, PL_Analog.TagName, PL_Analog.Value, 
Eng_Units.EU
,[LocalTime] = DATEADD(hour,-6,PL_Anaolg.Date_Time)
FROM PL_Analog
FULL JOIN Eng_Units
ON PL_Analog.TagName=Eng_Units.TagName
ORDER BY Date_Time

Everyday I learn something that somebody else already knew

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-14 : 16:39:27

Thanks jimf...

I had the basic idea but needed the syntax...with your help its working great so far.

This is just one step in a multi-step process so don't be suprised if you see me asking a few more questions over the next few days/weeks.

Thanks again.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-12-14 : 17:01:00

DATEADD(hour,-6,Date_Time) will only work correctly all the time if your server never switches to and from daylight savings time.

If your local time zone uses daylight savings time, then you will need to determine the correct offset for a particular UTC time.

CODO ERGO SUM

dchristopherson
Starting Member

10 Posts

Posted - 2010-12-15 : 09:49:00

quote:
Originally posted by Michael Valentine Jones

DATEADD(hour,-6,Date_Time) will only work correctly all the time if your server never switches to and from daylight savings time.

If your local time zone uses daylight savings time, then you will need to determine the correct offset for a particular UTC time.

I talked about this already with one of our other database guys and we've made the decision to "ignore" daylight savings time. Based on his experience DST creates problems with either a 1-hour gap in the collected data or an entire hours worth of data is rejected by SQL because of duplicate Date_Time/TagName values. A more acceptable solution for us is to define local time as Central Standard Time (UTC-6) and disregard daylight savings.

Thanks for raising the issue.

-Dylan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources