Still the same....
orientdb {db=baasbox}> select timestamp.format("'yyyy-MM-dd'") as dt from
FILE group by dt
0 item(s) found. Query executed in 0.02 sec(s).
One thing that I don't understand is that how will the following just group
by date and not by time:
** .format("'yyyy-MM-dd'T'HH:mm:ss.SSS'Z")
On Tuesday, April 21, 2015 at 10:45:00 PM UTC-7, Colin wrote:
>
> What happens if you simplify the format as an experiment?
>
> select timestamp.format("'yyyy-MM-dd") as dt from FILE group by dt
>
>
> On Tuesday, April 21, 2015 at 11:05:11 PM UTC-5, Kishy Kumar wrote:
>>
>> I tried the SQL you suggested and it didnt return any record.
>>
>> orientdb {db=baasbox}> select
>> timestamp.format("'yyyy-MM-dd'T'HH:mm:ss.SSS'Z") as dt from FILE group by dt
>> 0 item(s) found. Query executed in 0.021 sec(s).
>>
>> Thanks,
>> Kishy
>>
>> On Tuesday, April 21, 2015 at 9:58:34 AM UTC-7, Colin wrote:
>>>
>>> Hi Kishy,
>>>
>>> Try something like this:
>>>
>>> select timestamp.format("'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'') as dt from FILE
>>> group by dt
>>>
>>> Also, notice the double-quotes in the call to format since you have
>>> single quotes embedded in there.
>>>
>>> Let us know if that works better for you.
>>>
>>> -Colin
>>>
>>> Orient Technologies
>>>
>>> The Company behind OrientDB
>>>
>>>
>>> On Tuesday, April 21, 2015 at 11:33:43 AM UTC-5, Kishy Kumar wrote:
>>>>
>>>> Thanks for the suggestion.
>>>>
>>>> I try using date but it's not working.
>>>>
>>>> orientdb {db=baasbox}> select timestamp from FILE
>>>> ----+-----+----------------------------
>>>> # |@RID |timestamp
>>>> ----+-----+----------------------------
>>>> 0 |#-2:1|2015-03-28T00:20:27.027-0700
>>>> 1 |#-2:2|2015-03-28T00:20:27.027-0700
>>>> 2 |#-2:3|2015-03-28T00:20:27.027-0700
>>>> 3 |#-2:4|2015-03-28T00:20:27.027-0700
>>>> 4 |#-2:5|2015-03-28T00:20:27.027-0700
>>>> 5 |#-2:6|2015-03-29T00:20:27.027-0700
>>>> 6 |#-2:7|2015-03-29T00:20:27.027-0700
>>>> ----+-----+----------------------------
>>>>
>>>> I got an error when I used date() in the 'group by' clause.
>>>> ------
>>>> orientdb {db=baasbox}> select id, avg(rating) from FILE *group by
>>>> date(timestamp, 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'')*
>>>>
>>>> Error:
>>>> com.orientechnologies.orient.core.sql.OCommandSQLParsingException: Error
>>>> on
>>>> parsing command at position #64: Invalid keyword 'TIMESTAMP'
>>>> Command: select id, avg(driveData.rating) from _bb_feedback group by
>>>> date(timestamp, 'yyyy-MM-dd'T'HH:mm:ss.SSS'Z'')
>>>>
>>>> ------------------------------------------------------------------------^
>>>>
>>>> Am I using date incorrectly? Can you provide an example on how to use
>>>> date if I am doing it wrong.
>>>>
>>>> I can use subqueries to do something, but isn't there a simple way of
>>>> doing this?
>>>>
>>>> Thanks,
>>>> Kishy
>>>>
>>>> On Monday, April 20, 2015 at 5:22:19 AM UTC-7, [email protected] wrote:
>>>>>
>>>>> Hi,
>>>>>
>>>>> did you try the date() function (
>>>>> http://orientdb.com/docs/last/SQL-Functions.html#date)?
>>>>>
>>>>> Regards
>>>>>
>>>>> Le lundi 20 avril 2015 07:55:20 UTC+2, Kishy Kumar a écrit :
>>>>>>
>>>>>> I have a columns timestamp where I save the the datetime in this
>>>>>> 'yyyy-MM-dd'T'HH:mm:ss.SSS'-0700' format.
>>>>>>
>>>>>> It looks like this:
>>>>>>
>>>>>> ----+-----+----------+----------------------------
>>>>>> # |@RID |sysdate |timestamp
>>>>>> ----+-----+----------+----------------------------
>>>>>> 0 |#-2:1|19-04-2015|2015-03-28T00:20:27.027-0700
>>>>>> 1 |#-2:6|19-04-2015|2015-03-29T00:20:27.027-0700
>>>>>> ----+-----+----------+----------------------------
>>>>>>
>>>>>> Now I want to group records by just this format: 'yyyy-MM-dd', just
>>>>>> the date, and not the time.
>>>>>> I checked online for solution to it, and people recommended GROUP BY
>>>>>> CAST(d.log_date AS DATE).
>>>>>> Orientdb doesn't support CAST, I guess.
>>>>>>
>>>>>> How should I go about it then? Any help or suggestions are
>>>>>> appreciated.
>>>>>>
>>>>>
--
---
You received this message because you are subscribed to the Google Groups
"OrientDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
