CODEWITHSUNDEEP
encodingtimestamphex
Daniel
Daniel

Reputation: 35704

Determining timestamp data from hexadecimal data

I'm trying to extract timestamp data from a hexadecimal sequence and I've narrowed down the hex data where the timestamp is hidden

I've found two dates that are off by about two minutes

(2012-12-01 06:00:55 -0700)
A4  01  1B  FE  36  05  88  23  E4  40  

(2012-12-01 06:02:56 -0700)
A4  01  EF  F9  AF  10  88  23  E4  40  

(2012-12-01 06:00:49 -0700)
A4  01  67  5B  A5  04  88  23  E4  40  

(2012-12-02 06:00:47 -0700)
A4  01  D6  CF  74  04  A8  23  E4  40

More timestamps

A4  01  90  A1  B2  03  C8  2E  E4  40
A4  01  22  2D  E3  03  C8  2E  E4  40  
 -0800 
E0  01  FF  15  82  03  C8  2E  E4  40

I'm pretty sure, based on some other data that I was able to disqualify from being the date, that it is using little endian encoding

But this is about as far as I can get. I'm using this website http://fmdiff.com/fm/timestamp.html to convert the know timestamp to some common formats, but I'm just not seeing it.

Is there any other format (probably in .net) that I can try that this info is using?

Solved, thanks @Markus

here's the code that converts the (LE) hex

#include <Debug.au3>
#include <Date.au3>

_DebugSetup("Debug")

Func GetExcelTimestamp($dec)
   $excel_time = Dec($dec,3)
   $timeinms = ($excel_time-25569)*24*3600*1000
   $sTime = _DateAdd("s", Int($timeinms / 1000), "1970/01/01 00:00:00")

   _DebugOut($dec & " - " & $sTime)
   Return $sTime
EndFunc   ;==>GetExcelTimeDate


GetExcelTimestamp("40E423880536FE1B")
GetExcelTimestamp("40E4238810AFF9EF")
GetExcelTimestamp("40E4238804A55B67")
GetExcelTimestamp("40E423A80474CFD6")

Upvotes: 1

Views: 7114

Answers (1)

Markus A.
Markus A.

Reputation: 12752

Here's the Java code that will read the dates (explanation follows):

//-------------------------------------------------------------------------------
// Convert from hex to usable date value

long temp = 0x40E423880536FE1BL; // Decode 64-bit little endian (backwards) hex
//long temp = 0x40E4238810AFF9EFL; // example 2
//long temp = 0x40E4238804A55B67L; // example 3
//long temp = 0x40E423A80474CFD6L; // example 4

double excel_time = Double.longBitsToDouble(temp); // days since 1/1/1900

//-------------------------------------------------------------------------------
// Convert to something that Java can handle and output in correct timezone

long java_time = (long) ((excel_time-25569)*24*3600*1000); // ms since 1/1/1970
Date date = new Date(java_time);

SimpleDateFormat dateFormatGmt = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss");
dateFormatGmt.setTimeZone(TimeZone.getTimeZone("GMT")); // don't change timezone
System.out.println(dateFormatGmt.format(date));

The dates are stored as days since Jan 1st, 1900 (the way Excel stores them), converted to hex from a Double precision floating point in little endian format as you guessed correctly. The A4 01 which you included in the beginning is probably not part of the date.

Your dates are stored in the time-zone you posted (GMT-7), not in UTC.

Note:

It might be that the A4 01 is part of the number if it is some other floating point format, like an 80-bit extended format. But given that it is the same across your 4 examples, I rather think it's not.

Upvotes: 3

Related Questions