?
Solved

UTC int Convert to standard DateTime how?

Posted on 2005-03-03
24
Medium Priority
?
3,533 Views
Last Modified: 2008-01-09
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
Comment
Question by:donschli
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 5
  • +1
24 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13452355
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13452373
Perhaps it would help if you posted the structure of tm.
0
 

Author Comment

by:donschli
ID: 13452510
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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 27

Expert Comment

by:ptjcb
ID: 13452806
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
 

Author Comment

by:donschli
ID: 13452838
?
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13452913
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13453407
>>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
 

Author Comment

by:donschli
ID: 13453421
didnt know.  You can stay in if you wish, we havent gone anywhere.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13453431
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13453557
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13453598
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
 
LVL 27

Expert Comment

by:ptjcb
ID: 13453722
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
 

Author Comment

by:donschli
ID: 13453864
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13454546
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13454556
The keyword being equivalent.
0
 

Author Comment

by:donschli
ID: 13454950
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13455026
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
 

Author Comment

by:donschli
ID: 13455182
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
 
LVL 2

Accepted Solution

by:
klingzor earned 2000 total points
ID: 13455424
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13455951
klingzor,

Thank you.  I was beginning to think I was losing it (again).
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 13459744
" 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
 

Author Comment

by:donschli
ID: 13478732
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13478802
You are welcome. <g>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13478816
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question