SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Stored Procedure datetime parameter
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  09:54:15  Show Profile  Reply with Quote
Hello everyone,

I have stored procedure and I define datetime parameters and there is +4 hours difference between UTC and local system time. In my stored procedure I am moving daily data from one db to another db. My stored procedure scheduled to run at 02:30 AM every morning.

Here is my stored procedure;

ALTER PROCEDURE [dbo].[Usp_CopySCOMNworksPerfDataDailytoPerfData2DB]
AS
Declare @StartDate Datetime,
        @EndDate Datetime 
    

set @StartDate=GETDATE()-1
set @EndDate=GETDATE()

set @StartDate=dateadd(HH,20,convert(Varchar(10),@StartDate,101))
set @EndDate=Dateadd(SS,-1,DATEADD(HH,20,Convert(Varchar(10),@EndDate,101)))
select @Startdate as Startdate,  @EndDate as Enddate


Insert Into "PerfData2".dbo.VM5MinuteData (DateTime,Path,Objectname,Countername,SampleValue)
SELECT     Perf.vPerfRaw.DateTime, vManagedEntity.Path, vPerformanceRule.ObjectName, vPerformanceRule.CounterName, 
                      Perf.vPerfRaw.SampleValue
FROM         Perf.vPerfRaw INNER JOIN
                      vPerformanceRuleInstance ON 
                      Perf.vPerfRaw.PerformanceRuleInstanceRowId = vPerformanceRuleInstance.PerformanceRuleInstanceRowId INNER JOIN
                      vManagedEntity ON Perf.vPerfRaw.ManagedEntityRowId = vManagedEntity.ManagedEntityRowId INNER JOIN
                      vPerformanceRule ON vPerformanceRuleInstance.RuleRowId = vPerformanceRule.RuleRowId
WHERE     	((vPerformanceRule.CounterName = 'cpuUsedPct' and vPerformanceRule.ObjectName = 'VMGuest-cpu') or 
			(vPerformanceRule.CounterName = 'memoryActivePct' and vPerformanceRule.ObjectName = 'VMGuest-memory')) and
					  (Perf.vPerfRaw.DateTime between @Startdate and @Enddate) 
ORDER BY	vManagedEntity.Path, Perf.vPerfRaw.DateTime



I want to move the data daily for example; for example just '2009-07-08 00:00:00.00' to '2009-07-08 11:59:59.00' that is full one day in stored procedure, in order to get full one day data I defined

Startdate: 2009-07-07 8:00:00.00 + 4 hours = 2009-07-08 00:00:00.00
E nd Date: 2009-07-08 7:59:59.00 + 4 hours = 2009-07-08 11:59:59.00

my first question is;

Stored procedure runs every morning 02:30 AM and when I look at the destination database ("PerfData2") I see last last record datetime 6:30 ahead then I expected. How can I fix this?

Second Question is;
I am not sure if this is good approach or not? What is best practise for this? How should I edit my datatime parameter section?

I really appreciate any input;

Thank you.

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 07/10/2009 :  13:49:51  Show Profile  Reply with Quote
>>I see last last record datetime 6:30 ahead then I expected. How can I fix this?
Are you saying that some of the resulting rows had [DateTime] values beyond the limits of your date range (@StartDate,@EndDate)?

>>What is best practise for this?
Not really sure what "this" is. Is it that you are trying to define a "day" based on as 12 hours UTC timespan of midnight to 12:00 noon even though all your data is based on local time?

Also, shouldn't your "day" be 24 hours rather than 12 hours?

Be One with the Optimizer
TG
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  14:08:24  Show Profile  Reply with Quote
TG,
1- Yes records [DateTime] values beyond the limits of your date range.

2- in the stored procedure it is 24 hours. Please look at the below.

Declare @StartDate Datetime,
@EndDate Datetime


set @StartDate=GETDATE()-1
set @EndDate=GETDATE()

set @StartDate=dateadd(HH,20,convert(Varchar(10),@StartDate,101))
set @EndDate=Dateadd(SS,-1,DATEADD(HH,20,Convert(Varchar(10),@EndDate,101)))
select @Startdate as Startdate, @EndDate as Enddate


All I need is getting prior day data in utc time.

thank you.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4358 Posts

Posted - 07/10/2009 :  14:26:39  Show Profile  Reply with Quote
Why are you converting the date to a VARCAHR then doing a DATEADD on a varchar value?

How does teh UTC offset factor in to your query?

If you want the get all the data for the previos day try this:
Declare @StartDate Datetime,
@EndDate Datetime 

SET @StartDate = DATEADD(D, DATEDIFF(D, 0, DATEADD(D, -1, CURRENT_TIMESTAMP)), 0)
SET @EndDate = DATEADD(D, 1, @StartDate)

SELECT *
FROM MyTable
WHERE MyDate >= @StartDate
AND MyDate < @EndDate
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 07/10/2009 :  14:35:06  Show Profile  Reply with Quote
sorry I misread the results originally - I guess this is a 12 hour AM/PM time rather than 24 hour time:
quote:

Startdate: 2009-07-07 8:00:00.00 + 4 hours = 2009-07-08 00:00:00.00
E nd Date: 2009-07-08 7:59:59.00 + 4 hours = 2009-07-08 11:59:59.00



But I don't see any problem with your WHERE clause. Can you just run the SELECT statement without the INSERT and see that you are getting rows outside the range of whatever your @startDate and @endDate parameters resolve to?

this is the way I would have done it but I think we get the same results for @start and @end dates, right? I find it easier to make the endDate 00:00:00.000 of the next day and then say < @endDate

declare @s datetime
       ,@e datetime
       ,@UTC_Offset int

set @utc_offset = datediff(hour, getutcdate(), getdate())

--daterange for previous day:
select @s = dateadd(day, datediff(day, 1, getdate()), 0)
       ,@e = dateadd(day, datediff(day, 0, getdate()), 0)

--utc day range localized
select dateadd(hour, @utc_offset, @s)
       ,dateadd(hour, @utc_offset, @e)

OUTPUT:
2009-07-08 20:00:00.000 2009-07-09 20:00:00.000

--So your WHERE clause would be:
where [dateTime] >= dateadd(hour, @utc_offset, @s)
and    [dateTime] < dateadd(hour, @utc_offset, @e)


EDIT:
Mostly by Lamprey

Be One with the Optimizer
TG

Edited by - TG on 07/10/2009 14:38:12
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  14:52:03  Show Profile  Reply with Quote
Lamprey,

There is +4 hours difference between utc and system time. When I add records into database for example now time is :
2009-07-10 2:33:00.00 in the database it looks 2009-07-10 6:33:00.00 ok.

When I pull previous day data lets say:
2009-07-08 00:00:00.00
and
2009-07-08 23:59:59.00

When I run the query for previous day that is not going to be actual data because actual data is +4 hours ahead then the returned data right? or am I wrong?

Sorry but I am just confused this UTC offset things.

Your query works fine but am I getting accurate data for the previous day?



Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  14:56:35  Show Profile  Reply with Quote
TG, Lamprey,
Thank you I am going to try this one hope it helps : )
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 07/10/2009 :  15:07:31  Show Profile  Reply with Quote
Now you got me curious...

>>When I run the query for previous day that is not going to be actual data because actual data is +4 hours ahead then the returned data right? or am I wrong?

Depends - You need to define your terms.
What does the [dateTime] values represent and from what timezone are they based?

If you wanted to record the start time of a phone conversation you were having with your Friend in Greenwich. Your wrist watch says it's 9:00 AM. Your Friend's watch says its 1:00 PM. What time is stored in your database? Is your query is saying show me all the calls that started at 1:00 PM Greenwich time? So you need to ask: "WHERE [datetime]+4hrs = 1:00 PM." Which would return your call at 9:00 AM?

Be One with the Optimizer
TG
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  15:44:43  Show Profile  Reply with Quote
ok TG,
now it is getting more clear.


My time zone is GMT -5 Eastern (USA Canada) This is system time when I query the database for the records for example get me records for today after 3:00 PM; Now time is 3:42 PM in Pennsylvania

here is my Where clause ;

Where(Perf.vPerfRaw.DateTime > '2009-07-10 15:00:00.00' )

but results I am getting for 7:00 PM
2009-07-10 19:34:01.000
2009-07-10 19:34:01.000
2009-07-10 19:33:00.000
2009-07-10 19:33:00.000
2009-07-10 19:32:00.000
2009-07-10 19:32:00.000
2009-07-10 19:31:00.000

and when I run the these queries;


SELECT GETDATE()
2009-07-10 15:42:01.327


select GetUTCDate()
2009-07-10 19:42:01.327



you see the differences sql time is +4 hours ahead then the actual data record (system) time.

now I think I have to adjust my query something like this;

"WHERE [datetime]-4hrs = 1:00 PM



is that correct?


Edited by - nonsec on 07/10/2009 16:06:53
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 07/10/2009 :  16:14:33  Show Profile  Reply with Quote
those results satisfy your criteria in that they are: > '2009-07-10 15:00:00.00' maybe there are no rows between hour 15 and 19?

what happens if you query for:

Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

Do you still get values in hour 19? if so:

Is your Sql Server physically in the same time-zone you are? Are you directly connecting via Enterprise manager or are you using an application via a web service or something else to issue your request? I'm wondering if some middle tier is adjusting your input datetimes for the timezone offset.

Be One with the Optimizer
TG
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  16:43:52  Show Profile  Reply with Quote
TG,

This is one of Microsoft Product ( Operation Manager ) I believe by default application automatically insert data in that time zone. (+4 hours)

SQL Server and all servers and me, we are all in the same location and same time zone. I am using SQL server management studio.


"""""those results satisfy your criteria in that they are: > '2009-07-10 15:00:00.00' maybe there are no rows between hour 15 and 19?

A: yes there are rows between 15 to 19 because actual 15 is in the db is 19.

what happens if you query for:

Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'
"""""
A: I query db for the Where clause and of course it returns data because where we query this;
Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

it is actually equal to this.

Where Perf.vPerfRaw.DateTime >= '2009-07-10 11:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 12:00:00.00'

I think Operation Manager uses it own timezone offset when inserts data.

so far no luck.






Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
5937 Posts

Posted - 07/10/2009 :  17:05:28  Show Profile  Reply with Quote
This was a little ambiguous:
quote:
A: I query db for the Where clause and of course it returns data because where we query this;
Where Perf.vPerfRaw.DateTime >= '2009-07-10 15:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 16:00:00.00'

it is actually equal to this.

Where Perf.vPerfRaw.DateTime >= '2009-07-10 11:00:00.00'
AND Perf.vPerfRaw.DateTime < '2009-07-10 12:00:00.00'

I guess you mean that you got what you asked for - rows with datetime values in the 15-16 hour. But those times returned represent UTC times not GMT times.

So you need to use the offset. My code (after Lamprey's post) should do it although I may have reversed the offset logic. Here is the corrected version:

declare @s datetime
       ,@e datetime
       ,@UTC_Offset int

set @utc_offset = datediff(hour, getdate(), getutcdate())

--daterange for previous day:
select @s = dateadd(day, datediff(day, 1, getdate()), 0)
       ,@e = dateadd(day, datediff(day, 0, getdate()), 0)

--utc day range localized
select dateadd(hour, @utc_offset, @s)
       ,dateadd(hour, @utc_offset, @e)

--This proves we have our offset working right.  These should return the same value
select getutcdate() [UTCdate]
       ,dateadd(hour, @utc_offset, getDate()) [getDateWithOffsetApplied]


OUTPUT:
2009-07-09 04:00:00.000 2009-07-10 04:00:00.000


UTCdate                 getDateWithOffsetApplied
----------------------- ------------------------
2009-07-10 21:04:03.783 2009-07-10 21:04:03.783


--So your WHERE clause would be:
where [dateTime] >= dateadd(hour, @utc_offset, @s)
and    [dateTime] < dateadd(hour, @utc_offset, @e)


Be One with the Optimizer
TG
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/10/2009 :  17:17:14  Show Profile  Reply with Quote
TG,

Thank you, I am going to try this and let you know results. I really appreciate your effort and time.
Go to Top of Page

nonsec
Starting Member

26 Posts

Posted - 07/17/2009 :  08:49:50  Show Profile  Reply with Quote
TG, Lamprey,

Thank you very much, I really appreciate it. It worked : )
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000