• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3542
  • Last Modified:

UTC int Convert to standard DateTime how?

I have a UTC int, how do I convert this to a standard datetime.  Example, the UTC int 1104178047 is the date of 2005-03-02.  I need to do the conversion in tsql.  Both date and time are needed.  There are several proposed solutions I've found on the web, but they are incorrect.  Most involve using the DATEADD function.  But this does not work.  See expert exchange Solution Title: Convert UTC Date to MM/DD/YYYY HH MM SS for one such example.  The correct answer can be verified by using the C++ function GMTime.  This problem requires an understanding of UTC, a typical date solution will not work.
0
donschli
Asked:
donschli
  • 11
  • 7
  • 5
  • +1
1 Solution
 
Anthony PerkinsCommented:
So what are we missing here:  

Your definition of UTC is equivalent to the C++ GMTime and the definition for GMTime is:
Time represented as seconds elapsed since midnight (00:00:00), January 1, 1970, coordinated universal time (UTC).

And yet you say that is not the same as:
DATEADD(second, 1104178047, '1970-01-01')

I don't get it.
0
 
Anthony PerkinsCommented:
Perhaps it would help if you posted the structure of tm.
0
 
donschliAuthor Commented:
UTC is more involved than just counting seconds.  There are leap year and "leap seconds" considerations.
>>Perhaps it would help if you posted the structure of tm.
what is tm?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ptjcbCommented:
Here is the C++ function GMTime

/* GMTIME.C: This program uses gmtime to convert a long-
 * integer representation of coordinated universal time
 * to a structure named newtime, then uses asctime to
 * convert this structure to an output string.
 */

#include <time.h>
#include <stdio.h>

void main( void )
{
   struct tm *newtime;
   long ltime;

   time( &ltime );

   /* Obtain coordinated universal time: */
   newtime = gmtime( &ltime );
   printf( "Coordinated universal time is %s\n",
                               asctime( newtime ) );
}
0
 
donschliAuthor Commented:
?
0
 
ptjcbCommented:
UTC about once every year or two there is an extra second, called a "leap second." And I couldn't just use getutcdate and getdate and compare the difference between them as that would mean that you would have to reset the system time to whatever date and time that was needed to compare for each value in datecolumn. Since leap seconds are added 'all the time' the difference between two dates in UTC time and 'normal' time is not the same for say 1970-01-01 and 2003-08-30.
0
 
Anthony PerkinsCommented:
>>what is tm?<<
Since you are the one that talked about the C++ gmtime function, I assumed that you knew that it returned a pointer to a structure of type tm.

In any case, it looks like ptjcb has you covered so I will bow out.

Good luck.
0
 
donschliAuthor Commented:
didnt know.  You can stay in if you wish, we havent gone anywhere.
0
 
ptjcbCommented:
acperkins - yes, I assumed the same thing.

I should clarify - leap seconds are not added all the time - they are added, usually, at the end of Dec or end of June. You can also have negative leap seconds for some reason - I guess if the earth starts rotating west to east.

0
 
Anthony PerkinsCommented:
>>You can stay in if you wish, we havent gone anywhere.<<
Ok, since you insist <g>

>>UTC is more involved than just counting seconds. <<
UTC has nothng to do with counting seconds it is merely a time format and for the most part is equivalent to GMT.

The C++ gmtime function you quoted in your original question, on the other hand, is about counting seconds.  This is what the MS SDK has to say about it:

<quote>
Convert a time value to a structure.

struct tm *gmtime(
   const time_t *timer
);
struct tm *_gmtime64(
   const __time64_t *timer
);

Parameter

timer

Pointer to stored time. The time is represented as seconds elapsed since midnight (00:00:00), January 1, 1970, coordinated universal time (UTC).

Return Value
Return a pointer to a structure of type tm. The fields of the returned structure hold the evaluated value of the timer argument in UTC rather than in local time. Each of the structure fields is of type int, as follows:

tm_sec
Seconds after minute (0 – 59).

tm_min
Minutes after hour (0 – 59).

tm_hour
Hours since midnight (0 – 23).

tm_mday
Day of month (1 – 31).

tm_mon
Month (0 – 11; January = 0).

tm_year
Year (current year minus 1900).

tm_wday
Day of week (0 – 6; Sunday = 0).

tm_yday
Day of year (0 – 365; January 1 = 0).

tm_isdst
Always 0 for gmtime.

The gmtime, mktime, and localtime functions use the same single, statically allocated structure to hold their results. Each call to one of these functions destroys the result of any previous call. If timer represents a date before midnight, January 1, 1970, gmtime returns NULL. There is no error return.

_gmtime64, which uses the __time64_t structure, allows dates to be expressed up through 23:59:59, December 31, 3000, UTC, whereas gmtime only represent dates through 19:14:07 January 18, 2038, UTC. Midnight, January 1, 1970, is the lower bound of the date range for both these functions.

Remarks
The gmtime function breaks down the timer value and stores it in a statically allocated structure of type tm, defined in TIME.H. The value of timer is usually obtained from a call to the time function.

Note   The target environment should try to determine whether daylight savings time is in effect. The C run-time library assumes the United States rules for implementing the calculation of Daylight Saving Time (DST).

</quote>

So once again, I do not see what I am missing.
0
 
Anthony PerkinsCommented:
To answer your question in your other thread:
>>C++ has a function GMTime, which works great.  I'm looking for something similar in c#. <<

The equivalent function in .NET is:
System.DateTime.UtcNow, System.DateTime.ToUniversalTime
0
 
ptjcbCommented:
acperkins - check out this article:
http://www.michaelbrumm.com/simpletimezone.html

As for the conversion of UTC and to convert it to whatever time zone you want for presentation - SQL Server 2000 does not have these functions. Developers would have to write custom extended procedures to handle the formatting the dates and the offsets. For example, India is +4.5 hours. You could have them write something in C++, Java (check out this article:
http://www.sqlservercentral.com/columnists/chedgate/sqlandjavagoonadate.asp
or maybe C#).
0
 
donschliAuthor Commented:
To recap, I have an int representing a UTC.  Need to convert that int to a standard date time.  
>>So once again, I do not see what I am missing.
I dont see the solution.


>>System.DateTime.UtcNow and System.DateTime.ToUniversalTime
These do not take a UTC int and covert to standard date time.
0
 
Anthony PerkinsCommented:
>>I dont see the solution.<<
Fair enough.

>>These do not take a UTC int and covert to standard date time.<<
You will have to take that up with this MSDN article:
Run-Time Routines and .NET Framework Equivalents (Visual C++ Libraries)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/vclrfrun-timeroutinesnetframeworkequivalents.asp
0
 
Anthony PerkinsCommented:
The keyword being equivalent.
0
 
donschliAuthor Commented:
I still dont see a way for System.DateTime.UtcNow or System.DateTime.ToUniversalTime to convert a UTC int to a datatime.  They both appear to take standard time and convert to UTC.  If I'm incorrect, please example.
0
 
Anthony PerkinsCommented:
Perhaps it is because our definitions for UTC are different.  Again UTC is a way of representing time which is universal and in general the same as GMT.  So while it might be 2005-03-03 21:04:59.910 here locally, it is actually 2005-03-04 00:04:59.910 UTC.

What you are talking about (the gmtime() C++ function) is a way of representing the date as seconds since midnight January 1, 1970 UTC.

But I will bow out (again) as I don't appear to be making any headway and hopefully someone else will step up to the plate and set the record straight for us all.
0
 
donschliAuthor Commented:
To redefine for all,
I have a UTC date time, saved as an int, 1104178047, coming out of a Cisco phone system tracking application.  I need to convert it to a standard sql date time.  BOL says UTC is nbr of seconds since 1/1/1970.  So I though I could use datadiff or dateadd and just count the seconds,  then figure out my offset from GMT, but this doesn’t work.  

dateadd(s, 1104178047 , '1/1/1970') returns 2004-12-27 rather than  2005-03-02.

At first I thought the phone application was incorrect, but the more I looking into UTC, there seems to be more to the algorithm than just nbr of elapsed seconds.  Also, the c++ function gmtime will confirm 1104178047 to be  2005-03-02.  So I'm looking for a tsql function, or batch that will take an int (1104178047) and return the correct date (2005-03-02).
0
 
klingzorCommented:
I posted in the other thread but I'll post it here as well.

I tried the c++ gmtime function. It confirms 1104178047 to be  2004-12-27!

I think you accidentally passed the current datetime to the gmtime function, which is why you got 2005-03-02 (yesterday)
0
 
Anthony PerkinsCommented:
klingzor,

Thank you.  I was beginning to think I was losing it (again).
0
 
ptjcbCommented:
" So I'm looking for a tsql function, or batch that will take an int (1104178047) and return the correct date"

It does not exist. As I wrote before, t-sql does not have the tools to do this. It will do the simple 24*60*60 but it does not make allowances for offsets, leap seconds, or seasonal time changes (In October we have a 25-hour day because 1:30 AM shows up twice and how does t-sql know which one is which?). You are better off creating something in another language that does handle UTC conversion to the degree that you need.
0
 
donschliAuthor Commented:
klingzor, you are correct.  At the begining, my sample number 1104178047, was incorrect.  After your post, I created new data this morning, 1110216826, C++ correctly returns 3/7/05 11:33:46.  Also TSQL select dateadd(s, 1110216826 , '1/1/1970') returns correct.  I have tons of scripts to deal with GMT offsets now that I can see the forest.  Thanks again!!
0
 
Anthony PerkinsCommented:
You are welcome. <g>
0
 
Anthony PerkinsCommented:
Also, do not use this:
select dateadd(s, 1110216826 , '1/1/1970')

Use this (from my original comment):
DATEADD(second, 1104178047, '1970-01-01')

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now