add presto dttm format

[tarekrached]

the default dttm string was giving me GREATER_THAN_OR_EQUAL(timestamp, varchar) not registered errors with presto, this resolves that.

[coveralls]

Coverage remained the same at 82.165% when pulling 7284e1f on tarekrached:presto-dttm-converter into 7d27692 on airbnb:master.

[landscape-bot]

Code quality remained the same when pulling 7284e1f on tarekrached:presto-dttm-converter into 7d27692 on airbnb:master.

[wgzhao]

great work!

it just i want to need.

[mistercrunch]

Oh interesting, we use presto and the current auto-cast works for us. What version of presto are you using?

[tarekrached]

this is for 0.130, an earlier amazon sandbox release...

[mistercrunch]

Since Hive does not support a native datetime data type, a lot of our Presto timestamp columns are just ISO text, so this "fix" would break pretty much all of our slices.

The solution is probably in that other PR where you can define your date function on a per-table (but I'm asking the author to move to per-column) basis. Closing, sorry.

[tarekrached]

Right on, we'll monkey patch until we upgrade presto. Thanks for checking this out!

[tarekrached]

hey @mistercrunch we're running into this again. Does your comment above mean that all your presto timestamp comparisons are being done as lexical string comparisons? I don't think presto actually auto-casts varchars to timestamps (or at least I couldn't find anything in any of the release notes from 0.130 to 0.147)), so this means that if we do have timestamp columns, we would need to cast them to strings.

I appreciate the bind you're in due to storing ISO 8601 strings as dates, but shouldn't we use db-native timestamp comparisons, as in the oracle conversion directly before this change. Seems inconsistent to do string comparisons in some DBs and timestamp comparisons in others, especially when timestamps are available in presto.

Which PR were you referring to that has the per-table date functions?

[mistercrunch]

Right right. I think that PR was closed. We probably need something like this (per column casting function definition) as database can have DATE, DATETIME, TIMESTAMP and people often store dates as strings or epoch.

You're right about Presto, though it's odd as most people use Hive to load up the data that ultimately is made available in Presto, and Hive doesn't have date/time data types. The situation that Airbnb is in probably the most common one.

[tarekrached]

I think #506 (Dttm format) might be the PR you were talking about, but wouldn't that still be comparing timestamps lexically? Totally agree that should be on a per-column basis tho. Maybe I'm confused about Hive - didn't it get TIMESTAMP in 2011?

This feels like the wrong venue for this discussion - I'll open a new issue in a bit.