need binary format for 6 byte datetime string in mysql

running  Mysql 5.5.5 in ubuntu.  innodb engine

Here are some examples with the 6 byte hex string and the decoded message. We are looking for the decoding algorithm.  i.e.how to turn the 6 byte hex string into a date/time

This  forum makes a real mess of the full records .  See the nicely highlighted and formated word doc here.  
https://www.dropbox.com/s/zsqy9o2rw1h0e09/mysql%20datetime%20examples%20.docx?dl=0
link to formatted word document with the examples.

hex strings and decoded date/time pairs are below.

12 51 72 78 B9 46
2014-10-22 16:53:18

12 51 72 78 B9 54
2014-10-22 16:53:32

12 51 72 78 BA 13
2014-10-22 16:55:23

12 51 72 78 CC 27
2014-10-22 17:01:51

Full messages and some analysis below.   cleaner copy with helpfull highlighting is at the link.

Record 181 in your email export.  ~003061DA (h) offset in ibdata1
 +-----+-------+----------+---------+---------------------+----------+--------------+-------------+
| id  | extID | EventType| FieldID | Timestamp           | Duration | GrassCutting | SensorAlarm |
 
| 181 |  4    | IRDTrig  | S-D17-E | 2014-10-22 16:53:18 |  -1      |            0 |           0 |
00 35 80 00 00 B5 00 00 00 00 A1 23 80 00 00 00 2D 01 10 80 00 00 04 01 53 2D 44 31 37 2D 45 80 00 12 51 72 78 B9 46 00 00 00 00 00 00 F0 BF 80 80 07 00 05 B8
.5€..µ....¡#€...-..€....S-D17-E€..Qrx¹F......ð¿€€...¸.
 
| 182 |     4 | IRDNotTrig| S-D17-E | 2014-10-22 16:53:18 |   0.411 |            0 |           0 |
00 35 80 00 00 B6 00 00 00 00 A1 24 80 00 00 00 2D 01 10 80 00 00 04 02 53 2D 44 31 37 2D 45 80 00 12 51 72 78 B9 46 E7 FB A9 F1 D2 4D DA 3F 80 80 07 00 05 C0
.5€..¶....¡$€...-..€....S-D17-E€..Qrx¹Fçû©ñÒMÚ?€€...À
 
 
| 183 |     4 | IRDTrig     S-D17-E | 2014-10-22 16:53:18 | 0.257419 |            0 |           0 |
00 35 80 00 00 B7 00 00 00 00 A1 25 80 00 00 00 2D 01 10 80 00 00 04 01 53 2D 44 31 37 2D 45 80 00 12 51 72 78 B9 46 17 9E 97 8A 8D 79 D0 3F 80 80 07 04 05 C8
.5€..•....¡%€...-..€....S-D17-E€..Qrx¹F.ž—Š.yÐ?€€...È
 
 
| 184 |     4 | IRDNotTrig| S-D17-E | 2014-10-22 16:53:32 |   14.368 |            0 |           0 |
00 35 80 00 00 B8 00 00 00 00 A1 26 80 00 00 00 2D 01 10 80 00 00 04 02 53 2D 44 31 37 2D 45 80 00 12 51 72 78 B9 54 23 DB F9 7E 6A BC 2C 40 80 80 07 00 05 D0
.5€..¸....¡&€...-..€....S-D17-E€..Qrx¹T#Ûù~j¼,@€€...Ð
Compare to record 183
(h)46  (h)54 indicates 14 decimal seconds.
This is correct for the seconds difference.
[FN] record number
 
| 190 |    -1 | FlasherOn  | Test4   | 2014-10-22 16:55:23 |   100001 |            0 |           0 |
00 33 80 00 00 BE 00 00 00 00 A1 36 80 00 00 00 2D 01 10 7F FF FF FF 04 54 65 73 74 34               80 00 12 51 72 78 BA 13 00 00 00 00 10 6A F8 40 80 80 07 00 06 00
.3€..¾....¡6€...-...ÿÿÿ.Test4€..Qrxº......jø@€€....
Time difference to record 184 is 111 sec.  or 1:51  mm:ss
BA13-B954 =191 decimal  
13(h)-54(h)=-65 sec
 
 
| 208 |     4 | IRDTrig        | S-D17-E | 2014-10-22 17:01:51 |  250.608 |            0 |           0 |
 
00 35 80 00 00 D0 00 00 00 00 A1 48 80 00 00 00 2D 01 10 80 00 00 04 01 53 2D 44 31 37 2D 45 80 00 12 51 72 78 CC 27 FA 7E 6A BC 74 53 6F 40 80 80 05 00 06 90
.5€..Ð....¡H€...-..€....S-D17-E€..QrxÌ'ú~j¼tSo@€€....
Only the last 2 bytes changing
Compare to record 181
CC27-B946= 4833 decimal
17:01:51-16:53:18 =00:08:33 = 513 sec




 
[FN]
eng_mgrAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill BachPresident and Btrieve GuruCommented:
You're going to hate this one...

Type the first HEX value into Windows Calculator: 12517278B946
Check the radio button to switch to Decimal: 20141022165318
And add some formatting: 2014-10-22 16:53:18
Kent OlsenDBACommented:
Hi Mgr,

To the best of my knowledge, MySQL has never supported a 6-byte date, time, or timestamps.

This page from the MySQL dev pages gives us a couple of hints:

  https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

According to that page, MySQL 5.5.5 should be using a form of little endian to store the value.  However, your sample data clearly increments the right-most byte as the seconds change, indicating a big endian value.

Start with the hex value of the first sample, 12517278B949.  Its decimal value is 20141022165321.

20141022165321  That looks pretty easy to parse.  

divide by 1,000,000 and we get 20141022, the year, month and day (Year * 10000 + Month * 100 + Day).
mod 1,000,000 yields 165321.  That looks like (Hour * 10000 + Minute * 100 + Second)

The 5 second difference has several possible sources, but that's the jist of the calculation.  It may be that the format used internally by MySQL is not the same as the date/time values stored in the user data.


Good Luck!
Kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
eng_mgrAuthor Commented:
You nailed it.

We had tried hex to decimal early on and it appeared wrong so we tried a bunch of more complex schemes.  
kdo, Please contact me at frank%simrex.com replacing % with @.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.