The better solution would be to make the unix_timestamp function ignore the milliseconds (or round off)
That may run into the HIVE versus Drill On Mon, Jun 15, 2015 at 8:48 AM, Christopher Matta <[email protected]> wrote: > That's kind of annoying, would it make sense to support casting a timestamp > to an INT? That seems like it might be the most straightforward. > > Chris Matta > [email protected] > 215-701-3146 > > On Mon, Jun 15, 2015 at 11:13 AM, Andries Engelbrecht < > [email protected]> wrote: > > > Can use unix_timestamp, challenge is that Drill timestamp has millisec > > that causes issues. SO you can truncate the millisec. > > > > I.e. > > > > select to_timestamp(1432912733), > > unix_timestamp(substr(to_timestamp(1432912733),1,19)) from > `sys`.`version`; > > > > > > +------------------------+-------------+ > > | EXPR$0 | EXPR$1 | > > +------------------------+-------------+ > > | 2015-05-29 15:18:53.0 | 1432912733 | > > +------------------------+——————+ > > > > > > If you don’t truncate the millsec it causes an error. Perhaps there is > > another function that can handle millisec and not require the string > > function. > > > > > > select to_timestamp(1432912733), unix_timestamp(to_timestamp(1432912733)) > > from `sys`.`version`; > > > > Error: SYSTEM ERROR: java.lang.IllegalArgumentException: Invalid format: > > "2015-05-29 15:18:53.000" is malformed at ".000” > > > > —Andries > > > > > > > > On Jun 15, 2015, at 7:18 AM, Christopher Matta <[email protected]> wrote: > > > > > Is there a way to convert a timestamp string to unix time? > > > > > > Chris Matta > > > [email protected] > > > 215-701-3146 > > > > >
