search

betodealmeida / shillelagh

Making it easy to query APIs via SQL
MIT License
374 stars 50 forks source link

Google Sheet Adapter : Date Format throws "InvalidValue". #455

Closed sebint closed 1 week ago

sebint commented 3 weeks ago

Describe the bug I have data containing date in a google sheets where the format is "1/2/24 14:41"(Month/Day/Year Hour(24):Minutes). and when the data get parsed its throwing InvalidValue('1/2/24 14:41').

So I changed the format to "02/01/2024 14:41:36", but it still thew the same error "InvalidValue('/01/2024 14:41:36')" but now the date is missing.

Screenshot 2024-06-23 203953

Environment Python 3.11.9 Shillelagh 1.2.18

betodealmeida commented 1 week ago

@sebint can you share the spreadsheet with me (ralmeida@gmail.com)? Or a version with just the affected column, if the data is sensitive?

sebint commented 1 week ago

@betodealmeida, I have shared the spreadsheet with the given email address.

betodealmeida commented 1 week ago

@sebint thanks! I'm taking a look right now!

betodealmeida commented 1 week ago

Ah, this is similar to https://github.com/betodealmeida/shillelagh/issues/160.

Google API is returning for the column:

{'id': 'A', 'label': 'Send time', 'type': 'timeofday', 'pattern': 'M/D/YY h:mm'}

Even though the value is 1/2/24 14:41 the type is returned as timeofday for some reason. We have a function infer_column_type to fix problems like this, I'll make it handle this case as well (it only works with timestamps currently).

betodealmeida commented 1 week ago

@sebint I'll make a release with the fix today!

betodealmeida commented 1 week ago

@sebint 1.2.21 is out with the fix.