datetime is not meant for web3 but needs a Tableland alternative

[dtbuchholz]

User Story

I'm a developer who wants to insert values into a table with the latest timestamp, e.g., datetime('now'). However, Tableland SQL does not support this, which reduces SQL parity and increases friction.

Ideas

Create a Tableland-native SQL function called TBL_DATETIME() (or along those lines, like BLOCK_TIMESTAMP()) that would "magically":

• Insert the mined block's timestamp in a cell. But, this requires an extra API call to get the timestamp and other work with the parser, AST processing, and execution pipeline. I suppose that extra cost could be accounted for in a future Tableland, from a write fee perspective -- expensive SQL functions cost more.
• Insert the block.number (and/or maybe tx.hash) into a cell. This information already exists in the emitted events that validators process. But, this means devs have to do extra work to get the timestamp (i.e., the API call in the point above), which is the reason for wanting datetime in the first place. The SDK / CLI would be able to add this extra processing step as a feature, though, so maybe it's not the worst case -- but only offering block.number feels more like a different feature.

Additional Information

Briefly discussed on Discord here and related to a non-deterministic use case discovered here

[carsonfarmer]

An additional (bit more work) option would be to map "now" to block.datetime or similar. This would require some backend work and likely updates to the AST/Parser, but should it come up again in the future, might be a nice compromise.

[dtbuchholz]

Yeah that’d be much nicer imo too; anything to make it feel like the same ol SQL