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, ccas...@axway.com 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 orient-database+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
