Timestamp as UTC Integer

Question

I Have a legacy database with a field containing an integer representing a datetime in UTC

From the documentation: "Timestamps within a CDR appear in Universal Coordinated Time (UTC). This value remains independent of daylight saving time changes"

An example of a value is 1236772829.

My question is what is the best way to convert it to a .NET DateTime (in CLR code, not in the DB), both as the UTC value and as a local time value.

Have tried to google it but without any luck.

score 4 · Answer 1 · edited May 23 '17 at 12:08

You'll need to know what the integer really means. This will typically consist of:

An epoch/offset (i.e. what 0 means) - for example "midnight Jan 1st 1970"
A scale, e.g. seconds, milliseconds, ticks.

If you can get two values and what they mean in terms of UTC, the rest should be easy. The simplest way would probably be to have a DateTime (or DateTimeOffset) as the epoch, then construct a TimeSpan from the integer, e.g. TimeSpan.FromMilliseconds etc. Add the two together and you're done. EDIT: Using AddSeconds or AddMilliseconds as per aakashm's answer is a simpler way of doing this bit :)

Alternatively, do the arithmetic yourself and call the DateTime constructor which takes a number of ticks. (Arguably the version taking a DateTimeKind as well would be better, so you can explicitly state that it's UTC.)

You could also guess that @Jon's example is correct and see if your dates make sense if measured as an offset from from 1/1/1970. I'm guessing they do. — tvanfosson, Mar 13 '09 at 10:47

score 3 · Accepted Answer · answered Mar 13 '09 at 10:56

Googling that exact phrase gives me this Cicso page, which goes on to say "The field specifies a time_t value that is obtained from the operating system. "

time_t is a C library concept which strictly speaking doesn't have to be any particular implementation, but typically for UNIX-y systems will be the number of seconds since the start of the Unix epoch, 1970 January 1 00:00.

Assuming this to be right, this code will give you a DateTime from an int:

        DateTime epochStart = new DateTime(1970, 1, 1);

        int cdrTimestamp = 1236772829;

        DateTime result = epochStart.AddSeconds(cdrTimestamp);

        // Now result is 2009 March 11 12:00:29

You should sanity check the results you get to confirm that this is the correct interpretation of these time_ts.

Timestamp as UTC Integer

2 Answers2