AnsweredAssumed Answered

How to convert TIMESTAMP -> DATE for GROUP BY CLAUSES?

Question asked by MorkAfur on Oct 6, 2012
Latest reply on Oct 7, 2012 by MorkAfur

Title

How to convert TIMESTAMP -> DATE for GROUP BY CLAUSES?

Post

     I have what should be a simple query, but I can't see how to convert a TIMESTAMP field (TheDate field in the query below) to just a DATE field so I can GROUP by it.

     I have multiple entries for a given date (at different times, which I need too - hence a TIMESTAMP field type), but on the actual chart, I only want a chart showing the average values for each day.

     Here's what I have so far:

     >>> select avg(weight) as AvgWeight, TheDate from table1 group by TheDate  order by TheDate desc

     This query, however, as expected, gives me multiple entries for each date. (There's no mechanism yet to convert from TIMESTAMP->DATE).

     So, I was hoping for something like this to convert the TheDate (TIMESTAMP field) into a regular DATE field for the GROUP BY to work:

     >>> select avg(weight) as AvgWeight,  TheDate from table1 group by DATE(TheDate)  order by TheDate desc

     I'm using ExecuteSQL in the actual application, but this query I'm trying to get working first via a remote database console as FMP really doesn't help you out at all with any errors (other than the almost completely unhelpful… "?").

     Thanks in advance for any suggestions.

     - m
      

Outcomes