KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Metric ranges bug #2780

Open GHSix opened this topic on on Aug 2 - 4 comments

GHSix commented on Aug 2

On a query column, if I have

DATE_FORMAT(dt1, "%Y-%m-%d") dt

and try to

use \koolreport\dashboard\metrics\Value;
...
protected function fields()
    {
        return [
            Date::create('dt'),
            $this->sum(Number::create('qty'))
        ];

and select ranges as thisYear() or LastYear(), it will work, but with strange numbers that I was unable to identify from where they are coming.

As soon as I change the query to

DATE_FORMAT(dt1, "%Y-%m-01 00:00:00") dt

It will work right.

I guess it all boiles down to TimeBasedMetric.php where all function enforce 00:00:00 on returns, but I´m not sure.

I would expect Date to work with values as "Y-m-d" and DateTime for values as "Y-m-d h-m-s".

But, if I need to pass "Y-m-d h-m-s" to Date, or the Date or Time functions are bugged or, the Docs are missing something.

KoolReport commented on Aug 2

You try to use \koolreport\dashboard\fields\DateTime::create("dt") in fields() method with DATE_FORMAT(dt1, "%Y-%m-%d 00:00:00") dt in the query.

Another try: Please use simple query "SELECT dt1" and use Date::create("dt1")

Let see which one works.

GHSix commented on Aug 2

Ok, experiment a little more, I found that if I use

DATE_FORMAT(dt1, "%Y-%m-01")

(to get just Year and Month since I´m doing a GROUP BY in the query), it will give the worng values with ::Date or ::DateTime.

But if I change it to

DATE_FORMAT(dt1, "%Y-%m-01 00:00:00")

or

DATE_FORMAT(dt1, "%Y-%m-%d")

it will work fine with either, ::Date or ::DateTime.

Don´t know anymore if it is some bug showing up or me missunderstanding Date classes.

I don´t mind adding 00:00:00 to the query, just don´t understand the need.

GHSix commented on Aug 2

Also, there is a way to add "All Period / Results" or specific date ranges (ie. for "Last 2 years") to the range combo?

KoolReport commented 7 days ago

Since the Value and Trend metrics support showing value by hours if you select a specific day. That's why the favourite data type is DateTime.

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
bug

Dashboard