2 Replies Latest reply on Oct 7, 2012 6:34 AM by MorkAfur

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



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


           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

        • 1. Re: How to convert TIMESTAMP -> DATE for GROUP BY CLAUSES?

               I don't know if there's a function that will work within the SQL expression, but you can define a calculation field defined as:

               GetAsDate ( TheDate )

               and select Date as the return type. This will give you a value you can use in a Group By clause in your SQL or in a more convential chart based directly against a found set of records where you sort your records by this field and specify the "grouped" option for your chart.

               PS. That ? result IS absolutely maddening. I have found SeedCode's Free SQL Explorer helpful in reducing the frustration level.

          • 2. Re: How to convert TIMESTAMP -> DATE for GROUP BY CLAUSES?

                 Hi Phil,

                 Excellent! Creating another field to just have the date using GetAsDate was an idea I hadn't thought of. I was trying to use GetAsDate in the query itself and that didn't work. Thanks for this excellent suggestion. It worked!


                 I had a couple other formatting issues where FMP wouldn't give me the scale I wanted (vertically) and where the dates along the bottom were totally either wrong or the entire query's dates would appear in a huge list beneath the chart.

                 To fix these issues, all I ended up having to do was to switch the Data Source's "Chart Data" to use Current Record (Delimited Data) instead of current found set.

                 Just lots of expermentation required I guess.


                 With your own SQL queries, you might want to get a Java IDE that also has a database console. I use Intellij IDEA (jetbrains.com) which has a free version and a 30-day eval on the non-free version. Then, you could create a JDBC connection to your FMP solution and just do queries against it (once you enable JDBC in FMP's permissions, of course). It's all relatively easy to set up. On the plus side of this approach is that you can quickly see what your results are **and** get JDBC query error messages (totally lacking in FMP). On the negative side, there's no query builder tool so you have to know SQL. But, hey, nobody knows SQL "perfectly" so everyone fiddles with their queries getting them just right. I find that having this tool *and* FMP is a great combination. WIth IDEA, I can do a query I know works. Then it's just a matter of getting the SQL inside FMP to work. For me, having this remote capability (thanks to the FMP team for creating the remote capability to do queries like this!), is a wonderful sanity check. If there's a better way to do remote queries, I'd like to know about it. I just haven't found anything better or quicker.


                 Thanks again for your great replies!!!!!!!!!!!