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.
