[PDB-2494] PQL: support for date_trunc() function Created: 2016/03/07  Updated: 2017/03/15  Resolved: 2016/04/18

Status: Closed
Project: PuppetDB
Component/s: None
Affects Version/s: None
Fix Version/s: PDB 4.1.0

Type: New Feature Priority: Normal
Reporter: Karel Brezina Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Template:
Epic Link: PQL Phase 2
Story Points: 1

 Description   

For CM Dashboard, we need to get aggregated counts of report statuses for individual days, hours and minutes. Currently, we are not able to do that using a PQL query. However, it seems that adding support for one DB function (similar to "date_trunc" in PGS) and interval comparisons for dates to would satisfy our needs.

aggregation by days:

reports [date_trunc("day", end_time), status, count()]{ end_time >= "2016-03-01T00:00:00.000Z" and end_time < "2016-03-15T00:00:00.000Z" and nodes{ facts { name = "osfamily" and value = "RedHat"} } group by date_trunc("day", end_time), status}

aggregation by hours:

reports [date_trunc("hour", end_time), status, count()]{ end_time >= "2016-03-01T00:00:00.000Z" and end_time < "2016-03-02T00:00:00.000Z" and nodes{ facts { name = "osfamily" and value = "RedHat"} } group by date_trunc("hour", end_time), status}

aggregation by minutes:

reports [date_trunc("minute", end_time), status, count()]{ end_time >= "2016-03-01T10:00:00.000Z" and end_time < "2016-03-01T11:00:00.000Z" and nodes{ facts { name = "osfamily" and value = "RedHat"} } group by date_trunc("minute", end_time), status}



 Comments   
Comment by Ken Barber [ 2016/03/07 ]

What is the urgency around this, also - what project is this related to? I can't see any linked tickets here to give us any background as to the UI reason for this change.

Comment by Karel Brezina [ 2016/03/07 ]

@ken What about the support for date intervals? Currently, the following condition is not supported in PQL:

end_time >= "2016-03-01T00:00:00.000Z" and end_time < "2016-03-15T00:00:00.000Z"

Should I create a separate issue for that?

Comment by Ken Barber [ 2016/03/07 ]

Date comparison should work in AST, sounds like its a bug if it doesn't work - yes, raise another ticket.

Generated at Wed Jun 19 18:57:17 PDT 2019 using JIRA 7.7.1#77002-sha1:e75ca93d5574d9409c0630b81c894d9065296414.