ok, so there was a version discrepancy - but I still question why I
can't use start_timestamp in the group by clause here :
SELECT count(*) as count,category,((CAST(MS_FROM_TS(ts) as INT8)/?)*?)
as start_timestamp
FROM my_table
WHERE ts > ? AND ts < ?
GROUP BY category, start_timestamp
On Mon, Sep 22, 2008 at 10:27 PM, Brian Starke <[EMAIL PROTECTED]> wrote:
> Ok, new issue!
>
> So, I've created my MS_FROM_TS alias and tested it (works like a
> charm). Now, however, when I execute a query like the following :
>
> SELECT count(*) as count,category,((CAST(MS_FROM_TS(ts) as INT8)/?)*?)
> as start_timestamp
> FROM my_table
> WHERE ts > ? AND ts < ?
> GROUP BY category, start_timestamp
>
> I get this error :
>
> Caused by: org.h2.jdbc.JdbcSQLException: Column ts must be in the GROUP BY
> list;
>
> If I switch the group by to this instead (leaving everything else the same) :
>
> GROUP BY category, ((CAST(MS_FROM_TS(ts) as INT8)/?)*?)
>
> I still get the error. Any ideas?
>
> (I'm using the latest release, 1.0.78)
>
> On Fri, Sep 19, 2008 at 11:01 AM, Brian Starke <[EMAIL PROTECTED]> wrote:
>> I had not realized that ALIAS could be used in that way, thank you so
>> much for the tip - you've made my week.
>>
>> On Fri, Sep 19, 2008 at 10:43 AM, Thomas Mueller
>> <[EMAIL PROTECTED]> wrote:
>>>
>>> Hi,
>>>
>>>> get the long value of a TIMESTAMP column?
>>>
>>> In Java it's simple. What about writing a Java method:
>>>
>>> public class Test {
>>> public static long getTimeMillis(java.sql.Timestamp ts) {
>>> return ts.getTime();
>>> }
>>> }
>>> CREATE ALIAS TIME_MILLIS FOR "Test.getTimeMillis";
>>> SELECT TIME_MILLIS(NOW());
>>>
>>>> adding EPOCH to the time functions
>>>
>>> For negative year values? Unlike other databases, H2 support negative
>>> year values:
>>> CALL YEAR(TIMESTAMP '-100-01-01 10:00:00');
>>>> -100
>>>
>>> Regards,
>>> Thomas
>>>
>>> >>>
>>>
>>
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
