Hex() behavior for timestamp type

Help
1

How can I dump a timestamp value in hex format?

I tried to dump as follow, but always return dump of ascii string.

drop table test_ts;
create table test_ts (col1 timestamp(6));
insert into test_ts values (current_timestamp(6));

select col1, hex(col1), hex(col1:>blob)
from test_ts\G

singlestore> select col1, hex(col1), hex(col1:>blob)
→ from test_ts\G
*************************** 1. row ***************************
col1: 2023-03-27 19:31:53.197373
hex(col1): 323032332D30332D32372031393A33313A35332E313937333733
hex(col1:>blob): 323032332D30332D32372031393A33313A35332E313937333733
1 row in set (0.02 sec)

because size of timestamp(6) is 8 byte, I expected hex dump for 8 bytes.
but always return hex dump for string value.

Is there any way to get raw hex dump of timestamp type?
I’m looking to compare timestamps byte by byte in a replicate database environment.

thanks,

2

We don’t have any bit-level casting operations in SingleStoreDB, but you may be able to work around it. If you can convert something to a number, you can do:

json_array_pack_i64(‘[ number_as_string ]’)
to get a blob with the number in it. Then you can use HEX() to get the hex value of that.
So you can try
hex(json_array_pack_i64(concat(‘[’, unix_timestamp( timestamp_value ), ‘]’))

3

Thanks for your answer.

I was able to get the hex value in the way you told me, but I don’t know if the value matches the value stored in the singlestoredb blob file.

Sometimes user want to see stored values as-is for troubleshooting purposes. Unfortunately, singlestoredb tends to implicitly convert to string, which makes analysis difficult.

I expected hex( ) to do that, but it doesn’t.
How about adding a feature to dump column values for diagnostic purposes?

4

We have an internal feature request for something equivalent to a reinterpret_cast in C++, where you could cast to/from different types directly in binary. I put your use case on it for tracking. We’ll keep an eye on it. For now, I’m glad the approach using json_array_pack worked for you.