Converting C# UTC Date/Time value

[Mark Vande Kamp]

I have some data where some time stamps were logged as a date/time format that I don't know how to translate. They are 19-digit binary values that are readily translated by a C# function.

An example: In C# code, DateTime.FromBinary(5245773030495241607) returns 5/6/2010 12:06:46 AM.

Can anyone help me with writing SPSS syntax that will translate the 19-digit values I have into an SPSS date/time format?

Thanks,

Mark

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 02:26 PM 5/12/2010, Mark Vande Kamp wrote:

I have some data where some time stamps were logged as 19-digit binary values that are readily translated by a C# function.

An example: In C# code, DateTime.FromBinary(5245773030495241607) returns 5/6/2010 12:06:46 AM.

Can anyone help me with writing SPSS syntax that will translate the 19-digit values I have into an SPSS date/time format?

It's a pretty fair guess that you have an epoch-and-offset date format: that is, the number you have represents the time, in a some unit, since a chosen time in the past (the 'epoch').

(These are common now. SPSS uses seconds since midnight, October 14, 1582; Excel uses days starting with 1=1 January 1900; Unix uses seconds since midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970.)

It looks like you have access to a system that will generate such time stamps for 'right now', and convert them into readable dates and times. If so, you should be able to find the time unit and the epoch: calculate difference in, say, seconds between two time-stamps, and see what the numeric difference is between the binary values.

(The unit, whatever it is, is SHORT. The HP48 calculator's unit is 1/8192 seconds, and it counts from something like 0/00/0000; and ITS count is about a ten-thousandth of that number you give.)

The unit is the important thing. You don't need the epoch per se; you just need the timestamp for a convenient time not too far past. Take any other timestamp, subtract, convert the result to second or whatever, and add to your 'epoch' as represented in, say, SPSS format.

CAREFUL, though: SPSS cannot represent 19-digit numbers exactly. (SPSS carries 53 bits, or marginally less than 16 digits.) You'll have to read in your numbers as strings and break them apart. I'll guess that the epoch was a long time ago and there are many leading digits that won't change over times you're interested in; you can strip those, read the rest as a number, and proceed as above, having stripped the same digits from the time-stamp representation of your 'epoch'.

Or, perhaps you can read your data in C#, and convert your stamps into, say, seconds since something reasonable.
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Mark Vande Kamp]

Yes, it is an epoch-and-offset. According to Wikipedia, the epoch is 1 January, year 1, and the offset is 100 nanoseconds (a "tick"). However, I'm having trouble figuring out how to use that information.

Here's one false start -- 2000 years * 86400 sec/year * 10000000 ticks/sec = 1.728E+15.

But that's only a 16-digit number. Is the problem that my math is decimal and the 19-digit string is binary?

I don't write C# code and the example was generated for me by someone else. What a stupid problem.

Mark


I think I'm messedMy first guess was
On Wed, 12 May 2010, Richard Ristow wrote:

> At 02:26 PM 5/12/2010, Mark Vande Kamp wrote:
>
>       I have some data where some time stamps were logged as 19-digit
>       binary values that are readily translated by a C# function.
>
>       An example: In C# code, DateTime.FromBinary(5245773030495241607)
>       returns 5/6/2010 12:06:46 AM.
>
>       Can anyone help me with writing SPSS syntax that will translate
>       the 19-digit values I have into an SPSS date/time format?
>
>
> It's a pretty fair guess that you have an epoch-and-offset date format: that
> is, the number you have represents the time, in a some unit, since a chosen
> time in the past (the 'epoch').
>
> (These are common now. SPSS uses seconds since midnight, October 14, 1582;
> Excel uses days starting with 1=1 January 1900; Unix uses seconds since
> midnight proleptic Coordinated Universal Time (UTC) of January 1, 1970.)
>
> It looks like you have access to a system that will generate such time
> stamps for 'right now', and convert them into readable dates and times. If
> so, you should be able to find the time unit and the epoch: calculate
> difference in, say, seconds between two time-stamps, and see what the
> numeric difference is between the binary values.
>
> (The unit, whatever it is, is SHORT. The HP48 calculator's unit is 1/8192
> seconds, and it counts from something like 0/00/0000; and ITS count is about
> a ten-thousandth of that number you give.)
>
> The unit is the important thing. You don't need the epoch per se; you just
> need the timestamp for a convenient time not too far past. Take any other
> timestamp, subtract, convert the result to second or whatever, and add to
> your 'epoch' as represented in, say, SPSS format.
>
> CAREFUL, though: SPSS cannot represent 19-digit numbers exactly. (SPSS
> carries 53 bits, or marginally less than 16 digits.) You'll have to read in
> your numbers as strings and break them apart. I'll guess that the epoch was
> a long time ago and there are many leading digits that won't change over
> times you're interested in; you can strip those, read the rest as a number,
> and proceed as above, having stripped the same digits from the time-stamp
> representation of your 'epoch'.
>
> Or, perhaps you can read your data in C#, and convert your stamps into, say,
> seconds since something reasonable.
> ===================== To manage your subscription to SPSSX-L, send a message
> to [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command SIGNOFF SPSSX-L For a list of
> commands to manage subscriptions, send the command INFO REFCARD
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Richard Ristow]

At 06:36 PM 5/12/2010, Mark Vande Kamp wrote:

Yes, it is an epoch-and-offset. According to Wikipedia, the epoch is 1 January, year 1, and the offset is 100 nanoseconds (a "tick").

Bully for me then (buff, buff) for having essentially got there with just one number and my little HP48 calculator. 8-)

 However, I'm having trouble figuring out how to use that information.
Here's one false start --
2000 years * 86400 sec/year * 10,000,000 ticks/sec = 1.728E+15.

Part of your problem is the 86,400 seconds; there are 86,400 seconds in a DAY. A year is about 3.15E7 seconds; so, for 2010 years:

2010 years * 3.15E7 sec/year * 10,000,000 ticks/sec = 6.343E17 ticks

But that's still smaller than the number you posted, by a factor of 8.3. I don't understand it -- does anybody see what I'm missing?


But calculation does seem to show that a 'tick' is 100 nanoseconds, so what should work is to take any epoch you please, for example

5/6/2010 12:06:46 AM = 5245773030495241607 ticks

Store that epoch as an integral number of ticks, and an SPSS date-time value (not tested):

COMPUTE  EpochTicks = 5245773030495241607        /* won't work */.

NUMERIC  My_Epoch (DATETIME20).

COMPUTE  My_Epoch=NUMBER('5/6/2010',ADATE10)
                +NUMBER('12:06:46',TIME8)
                -TIME.HMS(12).

(That '-TIME.HMS(12)' is the correction needed, in this instance, to convert your AM time to 24-hour time.)

Then, if you have another time measured in ticks,

COMPUTE SecondsDiff=(NowTicks-EpochTicks)/1E7   /* won't work */.
NUMERIC NowTime (DATETIME20).
COMPUTE NowTime=MyEpoch+SecondsDiff.

Those calculations that won't work, won't work because they require precision exceeding what SPSS provides.

Briefly, you're going to have to drop, say, 6 trailing digits from your 'ticks' counts (assuming you have them as decimal strings), and, say, two leading digits.

All computations will work with values truncated that way, converted to SPSS numbers.

Sorry -- that isn't complete, but it's too late at night.

-Best of luck,
 Richard
===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

[Richard Ristow]

At 02:26 PM 5/12/2010, Mark Vande Kamp wrote:

>I have some data where some time stamps were logged as a date/time
>format that I don't know how to translate. They are 19-digit binary
>values that are readily translated by a C# function.
>
>An example: In C# code, DateTime.FromBinary(5245773030495241607)
>returns 5/6/2010 12:06:46 AM.

And at 06:36 PM 5/12/2010, added:

>It is an epoch-and-offset. According to Wikipedia, the epoch is 1
>January, year 1, and the offset is 100 nanoseconds [or 1E-7
>seconds], (a "tick").

That's the Windows NT system time, I think?

Anyway, how do your date-time values come into SPSS? If they come in
as SPSS string values, of 19 (non-blank) characters that are all
decimal digits, where the numeric value is that string, interpreted
as a decimal integer, here's what you can do; it refines, some, what
I wrote before.

Numeric value of the time tick

The resolution of those ticks is 100 nanoseconds, 1E-7 seconds.
That's probably far more than you need; probably, far more than the
clock's accuracy.

SPSS can store the exact number of milliseconds, though. To get that
value, drop the last four digits, then convert to numeric.

Suppose you have a time stamp in string variable "TStampString". To
store the number of milliseconds it represents in numeric variable
"TStampMsec", then (untested)

NUMERIC   TStampMsec (F15).
VAR LABEL TStampMsec 'Time stamp truncated to INTEGER MILLISECONDS'.
COMPUTE   TStampMsec = NUMBER(SUBSTR(LTRIM(TStampString),
                                      1,15),
                               F15).

(This truncates to the next lower millisecond. Rounding would take
another line of code; I'm not bothering, here.)

Your epoch

Probably your best course is to take as epoch your one known
correspondence between timestamp and date-time. For that, you need
two constant values:

*  The number of seconds in 5,245,773,030,495,241,607, expressed as
an integer number of milliseconds. (Drop fractions of a second to
match the date-time, which is given as an integral second.)

*  The date-time 5/6/2010 12:06:46 AM as an SPSS date-time value.

I'm following standard best practice, in making the two constants
scratch variables and initializing them only at the beginning of the
run. Again, untested:

NUMERIC
    #EpochDtime (DATETIME20) /* (SPSS) date-time of the epoch   .... */
    #EpochMsec  (F15)        /* Timestamp at the epoch, msec    ....  .

*  The epochal moment is one for which the correspondence of   ..... .
*  timestamp vand date-time is known:                          ..... .
*  Timestamp        5,245,773,030,495,241,607  (commas added)  ..... .
*  corresponds to   5/6/2010 12:06:46 AM                       ..... .

DO IF   $CASENUM EQ 1.
*  I.  Store the number of seconds in the timestamp as a       ..... .
*      numeric value, in milliseconds:                         ..... .

*      The number of seconds in the timestamp is its first     ..... .
*      twelve digits.                                          ..... .
.  COMPUTE #EpochMsec = 524577303049.

*      Express that value as milliseconds.                     ..... .
.  COMPUTE #EpochMsec = #EpochStamp * 1000.

*  II.  Store the corresponding date-time as an an SPSS        ..... .
*       date-time value.                                       ..... .
*       (12:06:46 AM is expressed as 00:06:46)                 ..... .

.  COMPUTE #EpochDtime = DATE.MDY(5,6,2010) /* 5/6/2010    */
                        + TIME.HMS(00,06,46) /* 12:06:46 AM */.
END IF.

Convert any timestamp to an SPSS date-time:

To convert EventStamp, as a 19-character string, to EventTime, an
SPSS date-time value corresponding down to milliseconds. The
time-stamp's value to milliseconds is stored in scratch variable
#Milliseconds, so it isn't kept in the file. Again, this is not tested:

NUMERIC #Milliseconds (F15).
COMPUTE #Milliseconds = NUMBER(SUBSTR(LTRIM(EventStamp),
                                       1,15),
                                F15).

NUMERIC EventTime  (DATETIME25.3).
COMPUTE EventTime = #EpochDtime
                   +(#Milliseconds-#EpochMsec)/1000.

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD