eng_mgr
asked on
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-
| 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-
| 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-
| 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-
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€...-...ÿÿÿ.Te
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-
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]
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-
| 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-
| 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-
| 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-
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€...-...ÿÿÿ.Te
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-
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]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 @.
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 @.
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