Datetime binary format

[Martin]

I am trying to read some data from a SQL-mapped SC database. A datetime field is embedded in a binary database field, apparently as 8 bytes. E.g 14 Jan 2004 14:58:23 is stored as [65,207,105,179,209,49,17,17] (decimal).

Can anyone help with translating this data into a date/time?

Thanks

[tommy]

This is a wild guess. Perhaps if You calculate a value from the 8 bytes it will be number of seconds (or if it is a really big number milliseconds) past 1/1 1970 ?

[Martin]

Hi Tommy

Tried this first, but the 8 bytes make an integer which is very large - would be something like 300000 years.

Wondered if there was a special format, such as first 2 bytes = year, next 2 bytes = month etc, but would like to know what the sequence is, whether byte order is reversed.

Might get it by trial and error, but hope someone could help first.

[pcasey]

It's number of seconds since some epoch or other (I want to say January 1, 0000).

One thing that might be getting you in trouble is that I believe the binary file format prepends a 4 bit type to any size data (the rc string is *not* byte alinged so any given byte may well contain data for more than one datum).

Thus, a binary unload of a date should be (if memory recalls):

4 bit type which should be 0011 (binary 3) followed by 64 bits of data (all servicecenter numbers are doubles).

So total datum size ought to be 68 bits, but many file operations can't write things that don't byte align properly, so in many cases a trailing 4 bits of garbage will be post-pended.

Note also that if you're unloading a *record* what contains a date, what you're actually unloading is:

Datum: structure (4 bits 1001)
Datum: date (4 bits 0011)
Datum: the date (64 bits data dependent)

[pcasey]

The way I had to figure out the file format, back in the day, was just to keep unloading very simple data that are different by only one bit.

Thus unload 12/25/2003 00:00:00 and 12/25/2003 00:00:01 and see how they differ.

Then internally translate 12/25/2003 00:00:00 into a number (63240768000), unload *it* and compare the unload of the number with the unload of the date to see how the type-bits work.

[Martin]

Patrick

Thanks for the reply. I had an idea this might be fun! The date is contained in a SQL image field which I have read into a byte array. I can decode the strings, but the date appears to be stored as decimal 61 (indicates datetime type?) followed by decimal 8 (length of field?) followed by the 8 bytes of data. I initially tried converting the 8 bytes to a int64, but following your reply I will try using a double.

[pcasey]

Can you just put the binary bit-stream out here so I can take a look? It's been so many years since I've played with the binary file format that I could be remembering things wrong, but a leading 61 doesn't sound right to me.

By decimal 61 you mean 00111101? (Hexi 3D)? If so you might be looking at:

Type 3 (first 4 bits), followed by leading 4 bits of data block. Since dates are always the same binary size, I don't think the RC format includes a size datum e.g. it isn't 4 bits type n bits size x bits data but rather 4 bits type x bits data.

By the way, today's date is the number: 63243137112
Binary representation: 111010111001100101011011000001011000
Hexi:EB995B058

[amethyst_andi]

We noticed that the stop field in the clocks table was mapped to SQL as varbinary. We wanted to use this field for reporting, so we pulled the table back into P4, modified the mapping changing the field to datetime (make sure you set SQL RC to false), and pushed it back out as datetime. ServiceCenter had no problem with this.