RE: Using hiveSQL to find content

[mellofello]

Awesome post! Thank you for sharing this information. It was easy to follow and I was able to query after about 15 minutes. My only comment was when I transfered 0.01 HBD to @hiveSQL, I received an error, so I needed to change the name to @hivesql.

I didn't realized the hiveSQL database is free. That's so cool. Do you know who is funding it to be free? Also, do know if I can query Splinterlands DEC transfers with this database? I am able to query this information through the Splinterlands API, however the transfers data is limited to a 30 day window. I do not know of another way to obtain transfers that are older than 30 days. Do you?

Comments

[abh12345]

Ah cool, thanks for checking it out and for that correction.

I didn't realized the hiveSQL database is free. That's so cool. Do you know who is funding it to be free?

It didn't used to be free, I paid 40 SBD/HBD a month for years, but:

https://peakd.com/me/proposals - see about 7 down.

Also, do know if I can query Splinterlands DEC transfers with this database?

This might be in TxCustoms, but I think it is on the sidechain. I don't deal with that one but perhaps @dalz or @gerber could be so kind to post a guide or a comment here :)

[mellofello]

I appreciate the guidance and for reaching out for more help.

[dalz]

SELECT
timestamp,
json_metadata

FROM Txcustoms
WHERE 
CONVERT(DATE,timestamp) BETWEEN '2020-12-01' AND '2021-02-28'
AND [tid] in ('ssc-mainnet-hive')
AND [json_metadata] like ('%"DEC"%')
AND [json_metadata] like ('%"null"%')

I use this query for DEC transfers to null ... you need to parse the json data as well

[abh12345]

Ah nice, so you could do something like this then...

SELECT top 10 
timestamp,
json_value(json_metadata, '$.contractPayload.symbol') as symbol,
json_value(json_metadata, '$.contractPayload.quantity') as quantity,
json_metadata
FROM Txcustoms
WHERE 
CONVERT(DATE,timestamp) BETWEEN '2021-01-01' AND '2021-02-28'
AND [tid] in ('ssc-mainnet-hive')
AND json_value(json_metadata, '$.contractPayload.symbol') = 'DEC'
AND json_value(json_metadata, '$.contractPayload.to')  = 'null'

[dalz]

That will do it :)
Testing it soon!

[mellofello]

Thanks abh12345, you and @dalz really came through for me today. I like how you parsed some of the json string.
FYI - when we use [tid] = ('ssc-mainnet-hive'), we get transfers on the Hive engine.
When we used [tid] = ('sm_token_transfer'), we get in-game transfers.

[abh12345]

Good to know :)

I've not looked at this table much, mainly out of fear of its size and the json parsing, but I think I'll pay a bit more attention to it in future. Thanks for opening up the conversation, and cheers dalz!

[mellofello]

Thanks so much for your prompt reply. Your query sample was very helpful. I was able to update the where clause to obtain exactly what I needed. I had no clue all this data was available all this time. This is very cool.