استفاده از دستور DateDiff و کار با ساعت در SQL Server

SQL Server 2008

SQL Server 2008

امروز قرار بود که برای یکی از جداول database یک چیزی رو حساب کنم فرض کنید یک دستگاه یک ساعتی روشن ( start ) می شود و یک ساعتی خاموش ( end ) در جدول دیتابیس شما ردیفهای بسیار زیادی مربوط به دستگاه خاص ( مورد محاسبه ) شما می شوند و بنابراین باید برای حساب کلیه ساعات فعال بودن دستگاه اول باید ساعات فعال در هر روز را محاسبه کنید . به این شکل که ساعت خاموش شدن را از ساعت روشن شدن کم کنید . برای اینکار دستورات زیادی وجود دارد که ساده ترین آنها استفاده از عملگر منها ( ) است یعنی برای مثل

select endTime – startTime as activeTime

اما خود T-SQL برای این کارها دستورات خوبی دارد و از آن جمله می توان به dateDiff اشاره کرد. DateDiff دستوری برای محاسبه و کار بر روی Date Time در SQL Server است . برای مثال دستورات زیر خروجی های زیر را تولید می کنند .

select datediff(yyyy, ‘2005-01-22 10:20:00′, ‘2008-01-22 16:20:00′) from myTable
خروجی => 3 سال

select datediff(m, ‘2008-01-22 10:20:00′, ‘2008-12-22 16:20:00′) from myTable
خروجی => 11 ماه

select datediff(d, ‘2008-01-22 10:20:00′, ‘2008-01-29 16:20:00′) from myTable
خروجی => 7 روز

select datediff(hh, ‘2008-01-22 10:20:00′, ‘2008-01-22 16:20:00′) from myTable
خروجی => 6 ساعت

select datediff(mi, ‘2008-01-22 10:20:00′, ‘2008-01-22 16:20:00′) from myTable
خروجی => 360 دقیقه

select datediff(s, ‘2008-01-22 10:20:00′, ‘2008-01-22 10:20:30′) from myTable
خروجی => 30 ثانیه

همچنین در دات نت فریم ورک هم برای کم کردن دو تاریخ از هم دستور Subtract وجوددارد که به شکل زیر استفاده می شود . این تابع یک DateTime را از یک DateTime دیگر کم می کند و یک TimeSpan خروجی می دهد . برای مثال :

DateTime firstDate = new DateTime(2008, 8, 1);
DateTime secondDate = DateTime.Now;
TimeSpan timeSpan = secondDate.Subtract(firstDate);
// Get Difference
Console.WriteLine(“Days : “ + timeSpan.Days.ToString());
Console.WriteLine(“Hours : “ + timeSpan.Hours.ToString());
Console.WriteLine(“Minutes : “ + timeSpan.Minutes.ToString());
// Get Total Hours
Console.WriteLine(“Total Hours “ + timeSpan.TotalHours.ToString());
به خروجی Hours و Total Hours دقت کنید

به خروجی Hours و Total Hours دقت کنید

البته این رو هم بگم که تیکه کدهای بالا رو خودم ننوشتم و از روی وب پیدا کردم و اینجا کپی کردم . اینجوری وقت کمتری ازم می گیره و بیشتر می تونم بنویسم . و یک راه حل دیگر استفاده از query زیر است . که برای کار مشابه خیلی خوب جواب می دهد . شما در اینجا یک ساعت شروع دارید و یک ساعت خاتمه و می خواهید در یک فیلد دیگر ساعت فعال ( که می شود ساعت خاتمه منهای ساعت شروع ) را محاسبه کنید . برای این کار از کد زیر استفاده کنید .

select convert(varchar, datediff(hour, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) % 24) + ‘ Hrs ‘ + convert(varchar, datediff(MINUTE, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) % 60) + ‘ Min’ as fieldNameforActiveTime

البته در این کد یک مشکلی هست و اینکه به مجموع ساعات شما یک ساعت اضافه می کند ، مثلا اگر خروجی صحیح باید 1 ساعت و 30 دقیقه بشود شما 2 ساعت و 30 دقیقه در خروجی می گیرید به این منظور قبل از اینکه بخواهید روز را حساب کنید ( یعنی قبل از 24 % ) باید 1- را بنویسید درواقع کد بالا باید بشود :

select convert(varchar, datediff(hour, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) -1 % 24) + ‘ Hrs ‘ + convert(varchar, datediff(MINUTE, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) % 60) + ‘ Min’ as fieldNameforActiveTime

البته اینکه مشکل از کجا هست رو خود من هنوز متوجه نشدم . اگر کسی متوجه شد بگه تا بقیه هم داستان براشون حل بشه . برای کد بالا براحتی میشه روز و ثانیه رو هم اضافه کرد فقط باید بعد از Min یک خط

convert(varchar, datediff(SECOND, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) % 60) + ‘ Sec’

و قبل از hour هم یک گزینه برای روز اضافه بشود به این شکل

convert(varchar, datediff(Hour, dbo.TableName.[StartTime], dbo.TableName.[EndTime]) / 24) + ‘ Day’

اضافه بشه ( به عملگر / و % در روز و ساعت دقت کنید . )

اما وقتی مجموع ساعت فعال رو به ازای هر روز درآوردید باید برای همه فیلدها که حالا بصورت nvarchar دراومدن ( با استفاده از کوئری بالا ) یک مجموع بنویسید که عملا بخاطر string بودن هر خروجی در این حالت غیر ممکنه . برای انجام اینکار باید یک Function نوشت و هر ساعت و هر دقیقه رو به ازای هر ردیف به اون پاس کرد . به این معنی که بجای کوئری بالا باید مثلا تابع GetHoursPerDay رو ( که خودتون باید بنویسید ) صدا بزنید. که اینجا یک مشکل وجود داره و اون اینکه این تابع به ازای هر ردیف یکبار اجرا میشه و پدر سرور رو درمیاره بنابراین راه خوبی نیست . بهتر اینه که راه دیگری امتحان بشه . من چیز دیگری به ذهنم نمی رسه اگر کسی از اساتید تجربه ای در این زمینه داره لطفا به ما هم بگه که چطوری میشه برای یک سری فیلد یک مجموع ساعات رو محاسبه کرد به شکلی که تابع یا store procedure مربوطه در بهینه ترین حالت باشه . همچنین اشخاصی که نحوه پیاده سازی صحیح GetHoursPerDay رو می دونن من رو در این زمینه راهنمایی کنن و در صورت امکان این تابع رو هم بنویسن . چون من این تابع رو اون قدر بد نوشتم که واقعا روم نشد اینجا بزارمش . با سپاس از همه دوستانی که اطلاعاتشون رو با سایرین تقسیم می کنن .

لینکهای مرتبط

یک مقاله بسیار خوب برای ایجاد توابع مربوط به Date در SQL توصیه می کنم حتما یک نگاهی بیاندازید .

4 Responses to استفاده از دستور DateDiff و کار با ساعت در SQL Server

  1. یک تابع بسیار عالی هم برای اینکار پیدا کردم .

    CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
    (
    @Firstdate DATETIME,
    @Seconddate DATETIME
    )
    /*
    Function written by Jens K. Suessmeyer, 07/22/2007
    http://www.sqlserver2005.de
    */
    RETURNS VARCHAR(10)
    AS
    BEGIN

    DECLARE @FirstdateMinutes INT
    DECLARE @SeconddateMinutes INT

    SELECT @FirstdateMinutes = DATEPART (hh,@Firstdate)*60 + DATEPART(mi,@Firstdate)
    SELECT @SeconddateMinutes = DATEPART (hh,@Seconddate)*60 + DATEPART(mi,@Seconddate)

    RETURN (
    SELECT
    CONVERT(VARCHAR(10), FLOOR(@SeconddateMinutes-@FirstdateMinutes) / 60) +
    ‘:’ +
    RIGHT(’00’ + CONVERT(VARCHAR(10), (@SeconddateMinutes-@FirstdateMinutes) – FLOOR((@SeconddateMinutes-@FirstdateMinutes) / 60)*60),2))
    END;

    بعد از execute کردن تابع بالا می تونید به شکل زیر از اون استفاده کنید . مثلا

    DECLARE @MyTable table
    ( RowID int IDENTITY,
    TravelTime smalldatetime
    )

    INSERT INTO @MyTable VALUES ( ’01/02/2007 01:00:00PM’ )
    INSERT INTO @MyTable VALUES ( ’01/03/2007 01:45:00PM’ )
    INSERT INTO @MyTable VALUES ( ’01/04/2007 03:00:00PM’ )
    INSERT INTO @MyTable VALUES ( ’01/04/2007 01:45:00PM’ )

    SELECT
    Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ‘:’, ‘.’ ), 2 ) AS int )) +
    ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ‘:’, ‘.’ ), 1 ) AS int )) / 60 ) ,
    Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ‘:’, ‘.’ ), 1 ) AS int )) % 60 )
    FROM @MyTable

    لینک این مطلب رو هم میزارم .
    http://www.windows-tech.info/15/8e4fd2ff5b62c938.php

  2. Vahid says:

    سلام
    من يك مثال تهيه كردم بر اساس جمع چندين ركورد ثبت شده. ببينيد مفيد است براي كار شما؟
    http://vahid.nasiri.googlepages.com/SQLQueryTst.sql

  3. این هم یک لینک خوب دیگه
    http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

  4. سهیل says:

    این Query مجموع همه اختلاف‌های بین دو ستون از نوع DATETIME رو به دقیقه حساب می‌کنه:

    select datediff(minute, cast(0 as datetime), cast(sum(cast(MYENDDATE as float) – cast(MYSTARTDATE as float)) as datetime)) from MYTABLE

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: