Ted's correct, it would be nice to be able to convert the UTC datetime column to whichever timezone I'm interested in, say 'America/New_York', as a timestamp so I can compare datasets that don't have UTC timestamps with those that do. Is this in the roadmap?
Chris Matta [email protected] 215-701-3146 On Tue, Mar 31, 2015 at 3:11 AM, Ted Dunning <[email protected]> wrote: > The original poster wasn't very clear. What they said could mean what > Andries provided (which is to determine which timezone that data refers > to). > > The way that I read the question was that they wanted to translate times to > be represented as the string formatted version of the same time in a > different timezone. > > > > On Mon, Mar 30, 2015 at 6:02 PM, Andries Engelbrecht < > [email protected]> wrote: > > > Here is the syntax for I could figure out for timezone. > > > > select to_timestamp('2015-03-30 20:49:59.0 UTC', 'YYYY-MM-dd HH:mm:ss.s > > z') as Original, to_char(to_timestamp('2015-03-30 20:49:59.0 UTC', > > 'YYYY-MM-dd HH:mm:ss.s z'), 'z') as New_TZ from sys.version; > > > > +------------+------------+ > > | Original | New_TZ | > > +------------+------------+ > > | 2015-03-30 20:49:00.0 | UTC | > > +------------+------------+ > > > > Using ‘Z’ will provide offset from UTC as opposed to the 3 letter > timezone > > code. > > > > —Andries > > > > > > On Mar 30, 2015, at 5:23 PM, Mehant Baid <[email protected]> wrote: > > > > > Currently in Drill there isn't support for 'timestamp with time zone' > > data type, all the timestamp/date information is stored in UTC and no > > timezone information is maintained, so AFAIK there isn't really a way to > > convert dates/timestamp to a specific timezone. However if your input > data > > contains timezone information Drill should be able to parse the input and > > convert it to UTC (if you use the 'z' format specifier). > > > > > > Thanks > > > Mehant > > > > > > On 3/30/15 3:08 PM, Christopher Matta wrote: > > >> I have a column that’s UTC, how can I convert this column to a > specific > > >> timezone? > > >> > > >> > > >>> select to_timestamp(columns[13], 'YYYY-MM-dd HH:mm:ss') as > > `datetime_utc` from `weather/2012` limit 10; > > >> +--------------+ > > >> | datetime_utc | > > >> +--------------+ > > >> | 2012-02-01 05:54:00.0 | > > >> | 2012-02-01 06:54:00.0 | > > >> | 2012-02-01 07:54:00.0 | > > >> | 2012-02-01 08:54:00.0 | > > >> > > >> Chris Matta > > >> [email protected] > > >> 215-701-3146 > > >> > > >> > > > > > > > >
