Set the type for a column

[halindrome]

Is your feature request related to a problem? Please describe.

When requesting data from a JSON backend, sometimes a backend returns time as seconds since the epoch. In this case, it would be really nice to interpret it as a date.

Describe the solution you'd like

Since the data is being parsed and injected into a sqlite cache anyway, I attempted to use syntax like DATETIME(somefield, 'unixepoch') as fieldName and it does indeed transform the data, but not in a way that is later treated as a timestamp.

Describe alternatives you've considered

I also considered using the sqlite CAST primitive, but that does not work to dates. I also considered forcing the data from the source to be in an ISO timestamp in the hope the underlying engine would magically just work - but I do not always have access to the data source and a local solution seems more interesting.

[betodealmeida]

Since the data is being parsed and injected into a sqlite cache anyway, I attempted to use syntax like DATETIME(somefield, 'unixepoch') as fieldName and it does indeed transform the data, but not in a way that is later treated as a timestamp.

Yeah, this is a problem. If the adapter returns a timestamp value we have affordances to convert it into a datetime object, but when the casting is done by SQLite we get a string back.

I tried registering converters and casting the column, but couldn't get it to work. Not sure how to do it. The apsw description for the column type always comes back as None, so it's hard to infer the expected type.