Visidata: [wishlist] Convert epoch integer string to date with `@`

[jsvine]

I often encounter seconds since the Unix epoch dates in datasets. Currently, casting these columns with @ does not seem to convert these values into datetimes. I can, of course, achieve my goals by creating a new column, =datetime.datetime.fromtimestamp(epoch), but it'd be neat if I could just press @.

I'm don't know quite enough, though, about VisiData's date-casting to tell whether this is a trivial feature to add or a more complicated one — so feel free to deprioritize!

Here's a small sample CSV for testing, in case it helps: epoch-example.csv.zip

[frosencrantz]

Yes, please make it part of the default date type.

As a workaround now, it is possible with the devel version with about three or four lines in your .visidatarc to create a custom type, if the whole column is epoch ints.

Per instructions from @saulpw in https://github.com/saulpw/visidata/issues/331#issuecomment-529310553 (and https://stackoverflow.com/questions/9744775/how-to-convert-integer-timestamp-to-python-datetime) you could do something like this: This is totally unrun, so might fail miserably. I'm happy to be corrected:

epoch_int = lambda v: datetime.datetime.fromtimestamp(int(v))
vdtype(epoch_int, 'E') # associate column type indicator in header
Sheet.addCommand('', 'type-epoch-int', 'curCol.type = epoch_int')

It seems from the Stack Overflow question, at times, you might also might want to deal with millisecond values, so it would be useful to have a multiplier for really large timestamps.

[saulpw]

This has already been in VisiData for a long time, but it's a bit hidden. If the underlying type is int or float, then the date 'parser' uses fromtimestamp to convert from an epoch timestamp. So for string epoch timestamps, you have to first convert the column to int type, then freeze the values into a new column with ' (tick), then convert that column to a date type, and it will interpret the timestamps correctly.

[jsvine]

Thanks, @saulpw! Those steps work for me on v2.-3dev. FWIW, however, on v1.5.2, I get this:

epoch #| epoch_frozen@| target_date ‖ 1527065746 | {:.0f} | 2018-05-23 ‖ 1527226805 | {:.0f} | 2018-05-25 ‖ 1555369701 | {:.0f} | 2019-04-15 ‖ 1562936560 | {:.0f} | 2019-07-12 ‖ 1565001748 | {:.0f} | 2019-08-05 ‖ 1572073293 | {:.0f} | 2019-10-26 ‖ 1580066594 | {:.0f} | 2020-01-26 ‖

Given the focus on v2, I’m not sure this is necessary to fix, but flagging in case it’s relevant. Command log here.

[saulpw]

Thanks for confirming, @jsvine. You're right that we're focused on v2 right now, so we won't backport a fix for that format/freeze bug to 1.5.2.

It would be neat to have the date type auto-convert epoch string times to dates, but as there is the reasonable workaround detailed above, for now it's going to sit as a wishlist item. If anyone wants to put some effort towards this, a PR would be most welcome.