Timebucket - Date Time Format - CSV

Yuen Wai Lee commented on Dec 27, 2018

HI

I'm using the Sakila CSV example that uses Time Bucket to group the data by month.

However I'm having difficulty replicating the format that Timebucket requires as "April-2018" / "1/4/2018" formats don't seem to work. The sample data works fine.

I'm very new to KoolReports so thanks in advance.

KoolReport commented on Dec 27, 2018

Hi Lee,

Welcome to KoolReport.

Could you please let me know the format of original date and the what kinds of bucket you want to get ( for example: group by week, month, year)

Thanks.

Yuen Wai Lee commented on Dec 27, 2018

I'm trying to Group by Month (could have same month in different year)

Original date format is "April 2018"

Yuen Wai Lee commented on Dec 28, 2018

Hi

Please assist with suggestion. Thanks!

KoolReport commented on Dec 28, 2018

Hi,

Sorry for my late reply. Let say the column time named "mytime" with format "April 2018" and you want to convert to "April" only, you will do:

->pipe(new DateTimeFormat(array(
    "mytime"=>array(
        "from"=>"F Y",
        "to"=>"F",
    )
)))

The TimeBucket is actually a short way to format the date time. Since your date is very custom so we use the DateTimeFormat.

Let me know if it works.

Yuen Wai Lee commented on Dec 28, 2018

Thanks for the tip...appreciate the help..

One more question.. after I convert the date how do I display the data? The current script is this (apologies, I'm not familiar with PHP).

protected function setup()
    {
        $this->src('sakila_rental')		
        ->pipe(new TimeBucket(array(
            "payment_date"=>"month"
        )))
        ->pipe(new Group(array(
            "by"=>"payment_date",
            "sum"=>"amount"
        )))
        ->pipe($this->dataStore('sale_accessory'));
    }

KoolReport commented on Dec 28, 2018

Please follow our example like this one to know how to setup the view file of report and use datastore as source for chart.

For example (in the view of report)

ColumnChart::create(array(
    "dataSource"=>$this->dataStore("sale_accessory"),
    "columns"=>array("payment_date","amount")
));

Yuen Wai Lee commented on Dec 28, 2018

This is what I see currently

Yuen Wai Lee commented on Dec 28, 2018

KoolReport commented on Dec 28, 2018

Something not right yet, seems that the payment_date in csv is not in standard format "Y-m-d H:i:s", if it is not in standard format, you will need to supply the format for it using ColumnMeta:

$this->src("sakila_rental")
->pipe(new ColumnMeta(array(
    "payment_date"=>array(
        "type"=>"datetime",
        "format"=>"F Y"; //Your format here
    )
)))

The full format you could find here.

Yuen Wai Lee commented on Dec 28, 2018

Thanks for the great help....think its my PHP knowledge...or lack of it!....will do more testing...

KoolReport commented on Dec 28, 2018

No worry! Any difficulty of PHP you may post here we will try to help!

KoolReport's Forum

Timebucket - Date Time Format - CSV #576

Build Your Excellent Data Report