CA Service Management

Expand all | Collapse all

Convert UTC date to local time

  • 1.  Convert UTC date to local time

    Posted Mar 11, 2015 03:46 PM

    I am building a SQL query to convert open_date of the call_req table to my local time. I know CA Service Desk universe is working with SQL objects, but I don't have to see the objects in SQL but it has the right conversion to my local time. I would like to know if somebody knows what is the formula in SQL. Thanks.



  • 2.  Re: Convert UTC date to local time

    Posted Mar 11, 2015 08:05 PM

    I use:

     

    DATEADD(ss, <date field> +/- <offset from UTC in seconds>, '1/1/1970')

     

    for example, if in UTC-5 and this is the open_date field, then this would be:

     

    DATEADD(ss, open_date - 18000, '1/1/1970')

     

    You can use other functions to get the final date format for your locale.

     

    I hope that helps,

     

    J.W.



  • 3.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 03:05 AM

    try this

     

    DATEADD(second,open_date-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))



  • 4.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 09:19 AM

    Thanks, the both formula worked fine but I am having problems with a particular hour in the night. It's between 11:00 pm and 12:00 am. I have an example using both formulas:

     

    DATEADD(ss, 1425189367 - 18000, '1/1/1970')

    DATEADD(second,1425189367-(DATEDIFF(second,GETDATE(),GETUTCDATE())), CAST('1970-01-01 00:00:00' AS datetime))

     

    The result is: 2015-03-01 00:56:07.000 but the correct result must be 2015-02-28 23:56:07.000. The problem is the right time. It's adding extra one hour for the time of 11:00 pm. For the rest of time, it's working perfectly the both formulas.



  • 5.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 09:47 AM

    You are showing 1425189367 in the formula.  Is this a literal value you placed there for our benefit?  If so, please check this source.  I show that this Unix epoch time-stamp converts to:  Sun, 01 Mar 2015 05:56:07 GMT.  So, using the formula this correctly would result in the value you show - 00:56:07.000


    The offset you are showing is UTC - 5.  (5 * 3600 seconds).  Is this correct?


    Regards,


    J.W.



  • 6.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 10:22 AM

    2015-03-12_9-13-31.png

    Okay, this Open Date Original is the column open_date from the table call_req. Open Date1 and Open Date2 are the formulas that you gave me. When I checked the details of the incident is showing the open as 02/28/2015 11:56 pm. I am using the offset 18000.



  • 7.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 10:04 AM

    did you consider daylight saving time?

    Maybe this causes the behavior you are describing.

     

    Correct UNIX timestamp for 2015-02-28 23:56:07.000  is  1425167767 an not the 1425189367

     



  • 8.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 10:31 AM

    I would think that if the local system's time has been updated for Daylight Savings, then Aleksandar's formula would account for this in the GETDATE() function and no additional adjustments would have to be made?  I would stick to Aleksandar's formula as it is more robust.


    J.W.




  • 9.  Re: Convert UTC date to local time

    Posted Mar 20, 2015 09:36 AM

    You are right. The problem is Daylight Saving Time.



  • 10.  Re: Convert UTC date to local time

    Posted Mar 23, 2015 09:52 AM

    I believe Aleksandar's formula is not considering DST. My problem is related with DST.



  • 11.  Re: Convert UTC date to local time

    Posted Apr 02, 2015 01:36 PM

    Try the following to address the DST issue:

    *******************************************************

    --Returns today's date

    DECLARE @todayLocalDate DATE                   

    SET @todayLocalDate = GETDATE()  

                              

    --Determines how many hours we are off from UTC

    DECLARE @dateTimeOffset DATETIMEOFFSET

    SELECT @dateTimeOffset =  CONVERT(VARCHAR(50), CAST(SYSDATETIMEOFFSET() AS DATETIMEOFFSET(0)), 126)

     

     

    --Converts to a string variable and chops off everything but the hours difference between PST and UTC.

    --'7:00' during DST, '6:00' after

    DECLARE @TZString VARCHAR(50)

    SET @TZString = RIGHT(@dateTimeOffset,4)

     

     

    --Defines a string value that can be used to populate the DATEDIFF for @ThisMorning

    DECLARE @TZOffset VARCHAR(50)

    SET @TZOffset = CASE @TZString

           WHEN '7:00' THEN '17:00:00'

           ELSE '16:00:00'

           END

     

     

    USE MDB;

     

    ************************************

    Then in the SELECT statement convert the date, i.e.:

     

    CONVERT (VARCHAR(19),(DateAdd(ss, CR.open_date, '1969-12-31 '+@TZOffset))) AS "Open Date"

     

    You'll just need to update the case statement to match your time zone.



  • 12.  Re: Convert UTC date to local time

    Posted Mar 12, 2015 10:07 AM

    this is the function for converting datetime to UNIX timestamp if you need it

    SELECT DATEDIFF(s, '1970-01-01', '2015-02-28 23:56:07.000')