Binning is not working on a TIMESTAMP field


#1

Hello,

Binning is not working on a TIMESTAMP field in the latest version 3.4 of MapD Immerse. Here are the steps that I followed:

  1. Imported 2 tables from redshift using SQLImporter into MapD server
  2. Created a view in SQL Editor in MapD Immerse by joining the 2 tables that were imported in step 1.
  3. Tried creating a new dashboard by using the view from step2 as the data source.
  4. Selected the TIMESTAMP field as the DIMENSION and tried doing the Bining at the month level. But it doesn’t do anything. Binning did work on a INT field from the view.

Can you please let me know what is causing this issue? This was working fine with version 3.3.

Also, I tried to use the single table with TIMESTAMP field as the data source and not the view. And then the binning works on the TIMESTAMP field. Not sure, why it is not working when the TIMESTAMP field is coming from a view instead.

Appreciate if you can help.

Thanks,
SJ


#2

Sorry to hear this. Can you provide details on your two table schemas (feel free to anonymize the columns if you like) and the create view query? Also what happens if you run a query in the SQL Editor that groups by date_trunc(month, timestamp_column)?

Regards


#3

Sure, here you go:

  1. Redshift Tables schemas (Have removed the extra columns from the tables to simplify this):
    Table t1 (batch_id INT, start_time TIMESTAMP, end_time TIMESTAMP);
    Table t2 (batch_id INT, category varchar(100))

  2. Imported above 2 tables in MapD using SQLImporter.

  3. Created the view as below:
    create view vw_t1_t2 as
    select
    t1.batch_id, t1.start_time, t1.end_time, t2.category
    from t1 join t2
    on t1.batch_id = t2.batch_id;

Hope this helps! Thanks.


#4

Yes, date_trunc(month, end_time) does help to group the results. However, the problem there is that it shows up as 1st of every month on the dashboard which is necessarily not correct. I mean, it should just show up as “Jan 2018” and not “1st Jan 2018” on the chart, because it becomes confusing that ways. Hope that makes sense.

Is there any way to remove the date from the TIMESTAMP after doing the trunc and just show month and year on the chart. I tried the usual date functions but didnt help because not everything works in MapD :(.


#5

Thanks for the info.

Were you doing an extract operation on the time chart? If so, can you see if a query grouping by extract(month from timestamp_column) works in the SQL Editor?

Regards


#6

Hi Darwin,

Thanks for your inputs. I don’t think “extract(month from end_time)” helps in my case. Because, this only returns the numeric value for the Month from the given timestamp (values returned being between 1 to 12). How will it work when I have some records for Jan 2017 and some records for Jan 2018. This extract operation will return value of “1” for both the cases and hence, it won’t show them as separate if I group by this extract thing, right?

Are you able to reproduce this issue where the Binning is not working on a TIMESTAMP field when it is sourced from a view that is created by joining 2 tables? Anything that you can think of that will work in my case? Thanks.


#7

Hi @sjain81,

Just to clarify, can you give exact instructions for what you are doing to make the binned chart? Having trouble replicating on my end but perhaps you are doing something different.

Regards


#8

Hi Darwin,

After I created the view (this is the final dataset that gets used on the dashboard as data source) as I explained above by joining 2 tables in MapD Immerse SQL Editor. Here are the next steps:

  1. Next, I created a new Dashboard using this view as a data source.
  2. Selected the end_time column from the data_source as a Dimension. Here is when it gives following options out of the box for this column: a) Binning b) Extract
  3. I selected Binning. By default, this is set to Auto. And because this column is a TIMESTAMP, I get following options for Binning: Decade, Year, Quarter, Month, Week, Day etc.
  4. With version 3.3, when I selected Month under Binning, my results will get grouped in different Months that appear in my dataset (rather than showing as individual dates, they get grouped). However, with version 3.4 this Binning is not working at all. Again, this is when I’m actually creating the dashboard in MapD Immerse.
  5. When I used a single table with a TIMESTAMP field as the data source (table, T1 from my example above), this Binning worked on end_time field. However, when I selected the view which was created by joining tables T1 and T2, this bining didn’t work on the end_time field.

Hope this makes sense.

Thanks!


#9

Hi @sjain81,

We were able to reproduce and are working on a fix that should be in the next release.

Thanks for your help detailing this!

Regards


#10

Thanks Darwin for confirming that you are able to reproduce. Glad that I was able to help reproduce this.

By any chance, would you know what is the expected timeframe for the next release? Appreciate if you can let me know. Thanks!


#11

Hi @sjain81,

The next release is likely to be out in mid-February.

Regards


#12

Hi @darwin, i’ve the same problem, however when execute the demo script “/mapd/insert_sample_data” the fields type - TIMESTAMP or DATETIME or DATE its work… However when create custom table with field type TIMESTAMP or DATETIME or DATE its not work.

You’ve some solution ?

Thaks !


#13

Hi @andrelucasti,

The new release (3.5) should be out tomorrow (fingers crossed) so please try with the new version when release and hopefully that should fix the issue.

Regards


#14

@andrelucasti @sjain81 we just released 3.5 this morning and would love to know if your issues are fixed.