There is a version of unix_timestamp that accepts a Date format pattern: elect to_timestamp(1432912733), unix_timestamp(to_timestamp(1432912733), 'yyyy-MM-dd HH:mm:ss.SSS') from `sys`.`version`; +------------------------+-------------+ | EXPR$0 | EXPR$1 | +------------------------+-------------+ | 2015-05-29 08:18:53.0 | 1432912733 | +------------------------+-------------+
On Mon, Jun 15, 2015 at 11:49 AM, Ted Dunning <[email protected]> wrote: > 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 > > > > > > > > > -- Steven Phillips Software Engineer mapr.com
