Re: TO_TIMESTAMP function returns in-correct results

2016-10-28 Thread Khurram Faraaz 
Works, thanks!

0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30 20:49:59.10
UTC', '-MM-dd HH:mm:ss.SSS z'));
++
| EXPR$0 |
++
| 2015-03-30 20:49:59.1  |
++
1 row selected (0.245 seconds)

On Fri, Oct 28, 2016 at 7:36 PM, Serhii Harnyk 
wrote:

> Example of the usage with "s" for second of minute and "S" for fraction of
> second:
>
> VALUES(TO_TIMESTAMP('2015-03-30 20:49:59.10 UTC', '-MM-dd HH:mm:ss.SSS
> z'))
>
> 2016-10-28 16:17 GMT+03:00 Khurram Faraaz :
>
> > Thanks Serhii.
> >
> > Can you please give me a working example of the usage with "s" for second
> > of minute and "S" for fraction of second.
> >
> > I tried with both those symbols, however Drill 1.9.0 (commit: a29f1e29)
> > does not honor those symbols when used from within the to_date function.
> >
> > On Thu, Oct 27, 2016 at 6:31 PM, Serhii Harnyk 
> > wrote:
> >
> > > Hello, Khurram
> > >
> > > http://joda-time.sourceforge.net/apidocs/org/joda/time/
> > > format/DateTimeFormat.html
> > >
> > > s   second of minute number55
> > > S   fraction of second   number978
> > >
> > >
> > >
> > > 2016-10-27 13:54 GMT+03:00 Khurram Faraaz :
> > >
> > > > All,
> > > >
> > > > I am on Drill 1.9.0 git commit ID : a29f1e29 on CentOS
> > > >
> > > > TO_TIMESTAMP function does not return correct results, note that the
> > > > minutes, seconds and milliseconds parts of timestamp are incorrect in
> > the
> > > > results
> > > >
> > > > {noformat}
> > > > 0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30
> > > 20:49:59.10
> > > > UTC', '-MM-dd HH:mm:ss.s z'));
> > > > ++
> > > > | EXPR$0 |
> > > > ++
> > > > | 2015-03-30 20:49:10.0  |
> > > > ++
> > > > 1 row selected (0.228 seconds)
> > > > {noformat}
> > > >
> > > > {noformat}
> > > > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST(TO_TIMESTAMP('2015-03-30
> > > > 20:49:59.10 UTC', '-MM-dd HH:mm:ss.s z') AS TIMESTAMP));
> > > > ++
> > > > | EXPR$0 |
> > > > ++
> > > > | 2015-03-30 20:49:10.0  |
> > > > ++
> > > > 1 row selected (0.265 seconds)
> > > > {noformat}
> > > >
> > > > This case returns correct results, when the same string used above is
> > > given
> > > > as input to CAST function, note that minutes mm, seconds ss and
> > > millisecond
> > > > s parts are honored
> > > >
> > > > {noformat}
> > > > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST('2015-03-30 20:49:59.10
> UTC'
> > AS
> > > > TIMESTAMP));
> > > > ++
> > > > | EXPR$0 |
> > > > ++
> > > > | 2015-03-30 20:49:59.1  |
> > > > ++
> > > > 1 row selected (0.304 seconds)
> > > > {noformat}
> > > >
> > > > Thanks,
> > > > Khurram
> > > >
> > >
> >
>

Re: TO_TIMESTAMP function returns in-correct results

2016-10-28 Thread Serhii Harnyk 
Example of the usage with "s" for second of minute and "S" for fraction of
second:

VALUES(TO_TIMESTAMP('2015-03-30 20:49:59.10 UTC', '-MM-dd HH:mm:ss.SSS
z'))

2016-10-28 16:17 GMT+03:00 Khurram Faraaz :

> Thanks Serhii.
>
> Can you please give me a working example of the usage with "s" for second
> of minute and "S" for fraction of second.
>
> I tried with both those symbols, however Drill 1.9.0 (commit: a29f1e29)
> does not honor those symbols when used from within the to_date function.
>
> On Thu, Oct 27, 2016 at 6:31 PM, Serhii Harnyk 
> wrote:
>
> > Hello, Khurram
> >
> > http://joda-time.sourceforge.net/apidocs/org/joda/time/
> > format/DateTimeFormat.html
> >
> > s   second of minute number55
> > S   fraction of second   number978
> >
> >
> >
> > 2016-10-27 13:54 GMT+03:00 Khurram Faraaz :
> >
> > > All,
> > >
> > > I am on Drill 1.9.0 git commit ID : a29f1e29 on CentOS
> > >
> > > TO_TIMESTAMP function does not return correct results, note that the
> > > minutes, seconds and milliseconds parts of timestamp are incorrect in
> the
> > > results
> > >
> > > {noformat}
> > > 0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30
> > 20:49:59.10
> > > UTC', '-MM-dd HH:mm:ss.s z'));
> > > ++
> > > | EXPR$0 |
> > > ++
> > > | 2015-03-30 20:49:10.0  |
> > > ++
> > > 1 row selected (0.228 seconds)
> > > {noformat}
> > >
> > > {noformat}
> > > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST(TO_TIMESTAMP('2015-03-30
> > > 20:49:59.10 UTC', '-MM-dd HH:mm:ss.s z') AS TIMESTAMP));
> > > ++
> > > | EXPR$0 |
> > > ++
> > > | 2015-03-30 20:49:10.0  |
> > > ++
> > > 1 row selected (0.265 seconds)
> > > {noformat}
> > >
> > > This case returns correct results, when the same string used above is
> > given
> > > as input to CAST function, note that minutes mm, seconds ss and
> > millisecond
> > > s parts are honored
> > >
> > > {noformat}
> > > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST('2015-03-30 20:49:59.10 UTC'
> AS
> > > TIMESTAMP));
> > > ++
> > > | EXPR$0 |
> > > ++
> > > | 2015-03-30 20:49:59.1  |
> > > ++
> > > 1 row selected (0.304 seconds)
> > > {noformat}
> > >
> > > Thanks,
> > > Khurram
> > >
> >
>

Re: TO_TIMESTAMP function returns in-correct results

2016-10-28 Thread Khurram Faraaz 
Thanks Serhii.

Can you please give me a working example of the usage with "s" for second
of minute and "S" for fraction of second.

I tried with both those symbols, however Drill 1.9.0 (commit: a29f1e29)
does not honor those symbols when used from within the to_date function.

On Thu, Oct 27, 2016 at 6:31 PM, Serhii Harnyk 
wrote:

> Hello, Khurram
>
> http://joda-time.sourceforge.net/apidocs/org/joda/time/
> format/DateTimeFormat.html
>
> s   second of minute number55
> S   fraction of second   number978
>
>
>
> 2016-10-27 13:54 GMT+03:00 Khurram Faraaz :
>
> > All,
> >
> > I am on Drill 1.9.0 git commit ID : a29f1e29 on CentOS
> >
> > TO_TIMESTAMP function does not return correct results, note that the
> > minutes, seconds and milliseconds parts of timestamp are incorrect in the
> > results
> >
> > {noformat}
> > 0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30
> 20:49:59.10
> > UTC', '-MM-dd HH:mm:ss.s z'));
> > ++
> > | EXPR$0 |
> > ++
> > | 2015-03-30 20:49:10.0  |
> > ++
> > 1 row selected (0.228 seconds)
> > {noformat}
> >
> > {noformat}
> > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST(TO_TIMESTAMP('2015-03-30
> > 20:49:59.10 UTC', '-MM-dd HH:mm:ss.s z') AS TIMESTAMP));
> > ++
> > | EXPR$0 |
> > ++
> > | 2015-03-30 20:49:10.0  |
> > ++
> > 1 row selected (0.265 seconds)
> > {noformat}
> >
> > This case returns correct results, when the same string used above is
> given
> > as input to CAST function, note that minutes mm, seconds ss and
> millisecond
> > s parts are honored
> >
> > {noformat}
> > 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST('2015-03-30 20:49:59.10 UTC' AS
> > TIMESTAMP));
> > ++
> > | EXPR$0 |
> > ++
> > | 2015-03-30 20:49:59.1  |
> > ++
> > 1 row selected (0.304 seconds)
> > {noformat}
> >
> > Thanks,
> > Khurram
> >
>

Re: TO_TIMESTAMP function returns in-correct results

2016-10-27 Thread Serhii Harnyk 
Hello, Khurram

http://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html

s   second of minute number55
S   fraction of second   number978



2016-10-27 13:54 GMT+03:00 Khurram Faraaz :

> All,
>
> I am on Drill 1.9.0 git commit ID : a29f1e29 on CentOS
>
> TO_TIMESTAMP function does not return correct results, note that the
> minutes, seconds and milliseconds parts of timestamp are incorrect in the
> results
>
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30 20:49:59.10
> UTC', '-MM-dd HH:mm:ss.s z'));
> ++
> | EXPR$0 |
> ++
> | 2015-03-30 20:49:10.0  |
> ++
> 1 row selected (0.228 seconds)
> {noformat}
>
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST(TO_TIMESTAMP('2015-03-30
> 20:49:59.10 UTC', '-MM-dd HH:mm:ss.s z') AS TIMESTAMP));
> ++
> | EXPR$0 |
> ++
> | 2015-03-30 20:49:10.0  |
> ++
> 1 row selected (0.265 seconds)
> {noformat}
>
> This case returns correct results, when the same string used above is given
> as input to CAST function, note that minutes mm, seconds ss and millisecond
> s parts are honored
>
> {noformat}
> 0: jdbc:drill:schema=dfs.tmp> VALUES(CAST('2015-03-30 20:49:59.10 UTC' AS
> TIMESTAMP));
> ++
> | EXPR$0 |
> ++
> | 2015-03-30 20:49:59.1  |
> ++
> 1 row selected (0.304 seconds)
> {noformat}
>
> Thanks,
> Khurram
>

TO_TIMESTAMP function returns in-correct results

2016-10-27 Thread Khurram Faraaz 
All,

I am on Drill 1.9.0 git commit ID : a29f1e29 on CentOS

TO_TIMESTAMP function does not return correct results, note that the
minutes, seconds and milliseconds parts of timestamp are incorrect in the
results

{noformat}
0: jdbc:drill:schema=dfs.tmp> VALUES(TO_TIMESTAMP('2015-03-30 20:49:59.10
UTC', '-MM-dd HH:mm:ss.s z'));
++
| EXPR$0 |
++
| 2015-03-30 20:49:10.0  |
++
1 row selected (0.228 seconds)
{noformat}

{noformat}
0: jdbc:drill:schema=dfs.tmp> VALUES(CAST(TO_TIMESTAMP('2015-03-30
20:49:59.10 UTC', '-MM-dd HH:mm:ss.s z') AS TIMESTAMP));
++
| EXPR$0 |
++
| 2015-03-30 20:49:10.0  |
++
1 row selected (0.265 seconds)
{noformat}

This case returns correct results, when the same string used above is given
as input to CAST function, note that minutes mm, seconds ss and millisecond
s parts are honored

{noformat}
0: jdbc:drill:schema=dfs.tmp> VALUES(CAST('2015-03-30 20:49:59.10 UTC' AS
TIMESTAMP));
++
| EXPR$0 |
++
| 2015-03-30 20:49:59.1  |
++
1 row selected (0.304 seconds)
{noformat}

Thanks,
Khurram