You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Siva <sb...@gmail.com> on 2015/05/01 11:49:19 UTC
TO_DATE is not working as expected
Hi,
Phoenix TO_DATE is truncating the time portion from date while converting
the date. Do I need to change the syntax? As per the documentation syntax
seems to be correct.
0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
+------------------------------------------+------------------------+
| createdate | TO_DATE(cf.createdate) |
+------------------------------------------+------------------------+
| 2015-03-17 00:00:00.000 | 2015-03-16 |
| 2014-04-28 13:31:22.687 | 2014-04-28 |
| 2014-04-29 08:42:09.317 | 2014-04-29 |
| 2014-04-29 08:42:18.167 | 2014-04-29 |
| 2014-04-29 08:42:31.963 | 2014-04-29 |
+------------------------------------------+------------------------+
5 rows selected (0.056 seconds)
Thanks,
Siva.
Re: TO_DATE is not working as expected
Posted by Siva <sb...@gmail.com>.
Hi Gabriel,
Thanks for your response.
You meant to say TO_DATE is actually holding the time portion but it is not
able to display it sqlline?
Can please correct me if I m wrong. createdate column is defined as string
on lead view.
Thanks,
Siva.
On Fri, May 1, 2015 at 3:38 AM, Gabriel Reid <ga...@gmail.com> wrote:
> Hi Siva,
>
> The TO_DATE function returns a java.sql.Date value, and the string
> representation of the java.sql.Date value is what you're seeing in your
> sqlline session.
>
> The internal long representation of the Date value coming out of TO_DATE
> will represent the date to millisecond granularity however.
>
> - Gabriel
>
> On Fri, May 1, 2015 at 11:50 AM Siva <sb...@gmail.com> wrote:
>
>> Hi,
>>
>> Phoenix TO_DATE is truncating the time portion from date while converting
>> the date. Do I need to change the syntax? As per the documentation syntax
>> seems to be correct.
>>
>> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
>> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>>
>> +------------------------------------------+------------------------+
>> | createdate | TO_DATE(cf.createdate) |
>> +------------------------------------------+------------------------+
>> | 2015-03-17 00:00:00.000 | 2015-03-16 |
>> | 2014-04-28 13:31:22.687 | 2014-04-28 |
>> | 2014-04-29 08:42:09.317 | 2014-04-29 |
>> | 2014-04-29 08:42:18.167 | 2014-04-29 |
>> | 2014-04-29 08:42:31.963 | 2014-04-29 |
>> +------------------------------------------+------------------------+
>> 5 rows selected (0.056 seconds)
>>
>>
>> Thanks,
>> Siva.
>>
>
Re: TO_DATE is not working as expected
Posted by Gabriel Reid <ga...@gmail.com>.
Hi Siva,
The TO_DATE function returns a java.sql.Date value, and the string
representation of the java.sql.Date value is what you're seeing in your
sqlline session.
The internal long representation of the Date value coming out of TO_DATE
will represent the date to millisecond granularity however.
- Gabriel
On Fri, May 1, 2015 at 11:50 AM Siva <sb...@gmail.com> wrote:
> Hi,
>
> Phoenix TO_DATE is truncating the time portion from date while converting
> the date. Do I need to change the syntax? As per the documentation syntax
> seems to be correct.
>
> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>
> +------------------------------------------+------------------------+
> | createdate | TO_DATE(cf.createdate) |
> +------------------------------------------+------------------------+
> | 2015-03-17 00:00:00.000 | 2015-03-16 |
> | 2014-04-28 13:31:22.687 | 2014-04-28 |
> | 2014-04-29 08:42:09.317 | 2014-04-29 |
> | 2014-04-29 08:42:18.167 | 2014-04-29 |
> | 2014-04-29 08:42:31.963 | 2014-04-29 |
> +------------------------------------------+------------------------+
> 5 rows selected (0.056 seconds)
>
>
> Thanks,
> Siva.
>
Re: TO_DATE is not working as expected
Posted by Siva <sb...@gmail.com>.
Thanks Gabriel, tried using JAVA and I could see time portion. Also tried
(to_date(c1) - to_date(c2))*24*60*60 in phoenix shell, which resulted the
difference in seconds.
@Nick: I found !outputformat option in sqlline, but it does not seem like
we can use this to change the output format of particular column. May be I
m not aware of.
Thanks,
Siva.
On Mon, May 4, 2015 at 9:41 AM, James Taylor <ja...@apache.org> wrote:
> FWIW, there's an option in sqlline that will cause it to display the
> full date granularity, but I don't know what it is. Maybe someone else
> does?
>
> Thanks,
> James
>
> On Mon, May 4, 2015 at 12:00 AM, Gabriel Reid <ga...@gmail.com>
> wrote:
> > Hi Siva,
> >
> > Yes, that's pretty much correct -- TO_DATE is returning a Date value,
> which
> > has millisecond granularity -- the fact that you're only seeing a date
> (with
> > no time component) is due to the way in which the Date is formatted, and
> not
> > it's internal value.
> >
> > - Gabriel
> >
> > On Sun, May 3, 2015 at 5:27 AM Siva <sb...@gmail.com> wrote:
> >>
> >> Any help on TO_DATE function?
> >>
> >> Thanks
> >>
> >> On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
> >>>
> >>> Hi,
> >>>
> >>> Phoenix TO_DATE is truncating the time portion from date while
> converting
> >>> the date. Do I need to change the syntax? As per the documentation
> syntax
> >>> seems to be correct.
> >>>
> >>> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
> >>> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
> >>>
> >>> +------------------------------------------+------------------------+
> >>> | createdate | TO_DATE(cf.createdate) |
> >>> +------------------------------------------+------------------------+
> >>> | 2015-03-17 00:00:00.000 | 2015-03-16 |
> >>> | 2014-04-28 13:31:22.687 | 2014-04-28 |
> >>> | 2014-04-29 08:42:09.317 | 2014-04-29 |
> >>> | 2014-04-29 08:42:18.167 | 2014-04-29 |
> >>> | 2014-04-29 08:42:31.963 | 2014-04-29 |
> >>> +------------------------------------------+------------------------+
> >>> 5 rows selected (0.056 seconds)
> >>>
> >>>
> >>> Thanks,
> >>> Siva.
> >>
> >>
> >
>
Re: TO_DATE is not working as expected
Posted by James Taylor <ja...@apache.org>.
FWIW, there's an option in sqlline that will cause it to display the
full date granularity, but I don't know what it is. Maybe someone else
does?
Thanks,
James
On Mon, May 4, 2015 at 12:00 AM, Gabriel Reid <ga...@gmail.com> wrote:
> Hi Siva,
>
> Yes, that's pretty much correct -- TO_DATE is returning a Date value, which
> has millisecond granularity -- the fact that you're only seeing a date (with
> no time component) is due to the way in which the Date is formatted, and not
> it's internal value.
>
> - Gabriel
>
> On Sun, May 3, 2015 at 5:27 AM Siva <sb...@gmail.com> wrote:
>>
>> Any help on TO_DATE function?
>>
>> Thanks
>>
>> On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
>>>
>>> Hi,
>>>
>>> Phoenix TO_DATE is truncating the time portion from date while converting
>>> the date. Do I need to change the syntax? As per the documentation syntax
>>> seems to be correct.
>>>
>>> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
>>> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>>>
>>> +------------------------------------------+------------------------+
>>> | createdate | TO_DATE(cf.createdate) |
>>> +------------------------------------------+------------------------+
>>> | 2015-03-17 00:00:00.000 | 2015-03-16 |
>>> | 2014-04-28 13:31:22.687 | 2014-04-28 |
>>> | 2014-04-29 08:42:09.317 | 2014-04-29 |
>>> | 2014-04-29 08:42:18.167 | 2014-04-29 |
>>> | 2014-04-29 08:42:31.963 | 2014-04-29 |
>>> +------------------------------------------+------------------------+
>>> 5 rows selected (0.056 seconds)
>>>
>>>
>>> Thanks,
>>> Siva.
>>
>>
>
Re: TO_DATE is not working as expected
Posted by Gabriel Reid <ga...@gmail.com>.
Hi Siva,
Yes, that's pretty much correct -- TO_DATE is returning a Date value, which
has millisecond granularity -- the fact that you're only seeing a date
(with no time component) is due to the way in which the Date is formatted,
and not it's internal value.
- Gabriel
On Sun, May 3, 2015 at 5:27 AM Siva <sb...@gmail.com> wrote:
> Any help on TO_DATE function?
>
> Thanks
>
> On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
>
>> Hi,
>>
>> Phoenix TO_DATE is truncating the time portion from date while converting
>> the date. Do I need to change the syntax? As per the documentation syntax
>> seems to be correct.
>>
>> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
>> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>>
>> +------------------------------------------+------------------------+
>> | createdate | TO_DATE(cf.createdate) |
>> +------------------------------------------+------------------------+
>> | 2015-03-17 00:00:00.000 | 2015-03-16 |
>> | 2014-04-28 13:31:22.687 | 2014-04-28 |
>> | 2014-04-29 08:42:09.317 | 2014-04-29 |
>> | 2014-04-29 08:42:18.167 | 2014-04-29 |
>> | 2014-04-29 08:42:31.963 | 2014-04-29 |
>> +------------------------------------------+------------------------+
>> 5 rows selected (0.056 seconds)
>>
>>
>> Thanks,
>> Siva.
>>
>
>
Re: TO_DATE is not working as expected
Posted by Gabriel Reid <ga...@gmail.com>.
Hi Siva,
Yes, that's pretty much correct -- TO_DATE is returning a Date value, which
has millisecond granularity -- the fact that you're only seeing a date
(with no time component) is due to the way in which the Date is formatted,
and not it's internal value.
- Gabriel
On Sun, May 3, 2015 at 5:27 AM Siva <sb...@gmail.com> wrote:
> Any help on TO_DATE function?
>
> Thanks
>
> On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
>
>> Hi,
>>
>> Phoenix TO_DATE is truncating the time portion from date while converting
>> the date. Do I need to change the syntax? As per the documentation syntax
>> seems to be correct.
>>
>> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
>> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>>
>> +------------------------------------------+------------------------+
>> | createdate | TO_DATE(cf.createdate) |
>> +------------------------------------------+------------------------+
>> | 2015-03-17 00:00:00.000 | 2015-03-16 |
>> | 2014-04-28 13:31:22.687 | 2014-04-28 |
>> | 2014-04-29 08:42:09.317 | 2014-04-29 |
>> | 2014-04-29 08:42:18.167 | 2014-04-29 |
>> | 2014-04-29 08:42:31.963 | 2014-04-29 |
>> +------------------------------------------+------------------------+
>> 5 rows selected (0.056 seconds)
>>
>>
>> Thanks,
>> Siva.
>>
>
>
Re: TO_DATE is not working as expected
Posted by Siva <sb...@gmail.com>.
Any help on TO_DATE function?
Thanks
On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
> Hi,
>
> Phoenix TO_DATE is truncating the time portion from date while converting
> the date. Do I need to change the syntax? As per the documentation syntax
> seems to be correct.
>
> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>
> +------------------------------------------+------------------------+
> | createdate | TO_DATE(cf.createdate) |
> +------------------------------------------+------------------------+
> | 2015-03-17 00:00:00.000 | 2015-03-16 |
> | 2014-04-28 13:31:22.687 | 2014-04-28 |
> | 2014-04-29 08:42:09.317 | 2014-04-29 |
> | 2014-04-29 08:42:18.167 | 2014-04-29 |
> | 2014-04-29 08:42:31.963 | 2014-04-29 |
> +------------------------------------------+------------------------+
> 5 rows selected (0.056 seconds)
>
>
> Thanks,
> Siva.
>
Re: TO_DATE is not working as expected
Posted by Siva <sb...@gmail.com>.
Any help on TO_DATE function?
Thanks
On Fri, May 1, 2015 at 2:49 AM, Siva <sb...@gmail.com> wrote:
> Hi,
>
> Phoenix TO_DATE is truncating the time portion from date while converting
> the date. Do I need to change the syntax? As per the documentation syntax
> seems to be correct.
>
> 0: jdbc:phoenix::/hbase> select "createdate", to_date("createdate",
> 'yyyy-MM-dd HH:mm:ss') from "lead" limit 5;
>
> +------------------------------------------+------------------------+
> | createdate | TO_DATE(cf.createdate) |
> +------------------------------------------+------------------------+
> | 2015-03-17 00:00:00.000 | 2015-03-16 |
> | 2014-04-28 13:31:22.687 | 2014-04-28 |
> | 2014-04-29 08:42:09.317 | 2014-04-29 |
> | 2014-04-29 08:42:18.167 | 2014-04-29 |
> | 2014-04-29 08:42:31.963 | 2014-04-29 |
> +------------------------------------------+------------------------+
> 5 rows selected (0.056 seconds)
>
>
> Thanks,
> Siva.
>