implement this : 10:05:15:456 - Event (Add to cart) SMS Node - Delay - 1 hr (60 min) SMS…
- User
implement this : 10:05:15:456 - Event (Add to cart) SMS Node - Delay - 1 hr (60 min)
SMS Trigger - 11:05
09:30 cron
start time: 08:30:00 end time: 08:30:59
11:05 cron
start time: 10:05:00 end time: 10:05:59 ---
package cubejs
import ( "encoding/json" "fmt" "time"
"omni-data-fetch-cron/model")
type QueryBuilder interface { Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) }
type queryBuilder struct{}
func NewQueryBuilder() QueryBuilder { return &queryBuilder{} }
func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) { var ( channelFilters []model.CubeQueryFilter actionFilters []model.CubeQueryFilter err error )
if channel.ConditionQuery != nil { channelFilters, err = parseConditionQuery(*channel.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err) } } if action.ConditionQuery != "" { actionFilters, err = parseConditionQuery(action.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err) } } filters := append(channelFilters, actionFilters...) filters = append(filters, []model.CubeQueryFilter{ { Member: "AttributionLogs.clientId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.ClientID)}, }, { Member: "AttributionLogs.workspaceId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.WorkspaceID)}, }, }...) delay := time.Duration(*channel.Delay) * time.Minute startTime := cronStartTime.Add(-delay).UTC().Format(time.RFC3339) endTime := cronStartTime.UTC().Format(time.RFC3339) return model.CubeQuery{ Query: model.CubeQueryPayload{ Dimensions: []string{}, Measures: []string{"AttributionLogs.count"}, Filters: filters, Order: map[string]string{ "AttributionLogs.eventCount": "desc", }, TimeDimensions: []model.TimeDimension{ { Dimension: "AttributionLogs.timestamp", DateRange: []string{startTime, endTime}, }, }, }, }, nil}
func parseConditionQuery(raw string) ([]model.CubeQueryFilter, error) { if raw == "" { return nil, nil }
var filters []model.CubeQueryFilter if err := json.Unmarshal([]byte(raw), &filters); err != nil { return nil, err } return filters, nil}
- Assistant
Your code snippet implements a Cube query builder in Go, which creates a query with a time range that considers a delay (in minutes) from the channel configuration, anchored to the cronStartTime parameter.
The scenario you described is:
- Event
Add to carthappens at 10:05:15:456 - You want to send an SMS after a delay of 1 hour (60 minutes)
- Cron jobs run at 09:30 and 11:05 (and their respective start/end time windows)
- Start/end time windows shown for crons like 08:30:00 to 08:30:59, 10:05:00 to 10:05:59 etc.
Understanding Your Scenario vs Code
Your code calculates the query time range as:
startTime = cronStartTime - delay endTime = cronStartTimeFor example, if
cronStartTime= 11:05,delay= 60 mins, then:startTime = 11:05 - 60min = 10:05 endTime = 11:05This time window queries for events from 10:05 to 11:05, which fits your example perfectly — you trigger an SMS at 11:05 based on the event that happened at 10:05.
Suggested improvements:
1. Clarify usage of start/end time for cron consistency
The cron jobs have their own start/end time window to query a 1-minute interval. Your Cube query is set with a wider time range (60 mins window, considering delay). I suggest you explicitly integrate the cron window (like 10:05:00 to 10:05:59) to build your query time ranges for filtering the event records exactly in that corresponding minute.
2. Example usage:
- At 11:05 cron, you search for events in 10:05:00 - 10:05:59 (which is
startTime = cronStartTime - delay) - Your Cube query time range should be for that 1 minute window, not the full 60 minutes.
Code to adjust query time range to exactly that 1 min interval (your cron window):
func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) { var ( channelFilters []model.CubeQueryFilter actionFilters []model.CubeQueryFilter err error ) if channel.ConditionQuery != nil { channelFilters, err = parseConditionQuery(*channel.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err) } } if action.ConditionQuery != "" { actionFilters, err = parseConditionQuery(action.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err) } } filters := append(channelFilters, actionFilters...) filters = append(filters, []model.CubeQueryFilter{ { Member: "AttributionLogs.clientId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.ClientID)}, }, { Member: "AttributionLogs.workspaceId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.WorkspaceID)}, }, }...) delay := time.Duration(*channel.Delay) * time.Minute smsTriggerStart := cronStartTime.Add(-delay).UTC() smsTriggerEnd := smsTriggerStart.Add(time.Minute).UTC() // 1 minute window startTime := smsTriggerStart.Format(time.RFC3339) endTime := smsTriggerEnd.Format(time.RFC3339) return model.CubeQuery{ Query: model.CubeQueryPayload{ Dimensions: []string{}, Measures: []string{"AttributionLogs.count"}, Filters: filters, Order: map[string]string{ "AttributionLogs.eventCount": "desc", }, TimeDimensions: []model.TimeDimension{ { Dimension: "AttributionLogs.timestamp", DateRange: []string{startTime, endTime}, }, }, }, }, nil }
Explanation:
cronStartTime— the actual run time of the cron (e.g., 11:05:00)delay— delay in minutes from the channel configuration (e.g., 60 mins)smsTriggerStart=cronStartTime - delay(e.g., 11:05 - 60mins = 10:05)smsTriggerEnd=smsTriggerStart + 1 minute(e.g., 10:06)- Thus you query exactly for events in the cron-window of 10:05 – 10:05:59 (to match your event timestamp of 10:05:15)
This matches the SMS trigger time and delay logic exactly.
Let me know if you want me to add unit tests or additional logic for handling the
Add to cartevent or for cron scheduling! - Event
- User
make a main file to test a query building
- Assistant
Certainly! Below is a simple
main.gofile that demonstrates how to use yourcubejspackage'sNewQueryBuilderandBuildmethod to build a Cube query with a sampleChannelNode,ActionNode, and a sample cron start time.
Assumptions:
- You have your
modelpackage already defined with the structures used (ChannelNode,ActionNode,CubeQuery,CubeQueryFilter,TimeDimension, etc.). - Your
cubejspackage is correctly located and imported in your project.
main.gopackage main import ( "encoding/json" "fmt" "time" "yourmodule/cubejs" // replace with your actual module path "yourmodule/model" // replace with your actual module path ) func main() { // Create dummy channel and action nodes for testing delay := 60 // 60 minutes delay for SMS trigger channel := model.ChannelNode{ ClientID: 123, WorkspaceID: 456, Delay: &delay, // Example condition query: filter for specific property, JSON string format ConditionQuery: nil, // or &channelConditionQuery, see below } // Example condition query: only events with name "Add to cart" actionConditionJSON := `[{"member":"AttributionLogs.eventName","operator":"equals","values":["Add to cart"]}]` action := model.ActionNode{ ConditionQuery: actionConditionJSON, } // Simulate the cron start time at 11:05:00 (time when SMS is triggered) cronTimeStr := "2024-06-12T11:05:00Z" cronStartTime, err := time.Parse(time.RFC3339, cronTimeStr) if err != nil { panic(fmt.Errorf("failed to parse cron start time: %w", err)) } qb := cubejs.NewQueryBuilder() // Build Cube query query, err := qb.Build(channel, action, 0, 100, cronStartTime) if err != nil { panic(fmt.Errorf("failed to build query: %w", err)) } // Print the generated query structure prettyJSON, _ := json.MarshalIndent(query, "", " ") fmt.Println("Generated Cube Query:") fmt.Println(string(prettyJSON)) }
What this example does:
- Creates a
channelnode with a 60-minute delay. - Creates an
actionnode with a filter condition targeting"Add to cart"event. - Sets the cron start time to
2024-06-12T11:05:00Zas in your example. - Calls your
Buildmethod to get the query. - Marshals the query struct into pretty JSON and prints it.
Sample output would look like:
{ "Query": { "Dimensions": [], "Measures": [ "AttributionLogs.count" ], "Filters": [ { "Member": "AttributionLogs.eventName", // from action.ConditionQuery "Operator": "equals", "Values": [ "Add to cart" ] }, { "Member": "AttributionLogs.clientId", "Operator": "equals", "Values": [ "123" ] }, { "Member": "AttributionLogs.workspaceId", "Operator": "equals", "Values": [ "456" ] } ], "Order": { "AttributionLogs.eventCount": "desc" }, "TimeDimensions": [ { "Dimension": "AttributionLogs.timestamp", "DateRange": [ "2024-06-12T10:05:00Z", "2024-06-12T10:06:00Z" ] } ] } }
Notes
- Replace
"yourmodule/cubejs"and"yourmodule/model"with your actual Go module import paths. - You can add additional fields or custom logic depending on your real model.
Let me know if you want me to help with
modelstruct definitions or runnable full example! - You have your
- User
did not met withour expectations
- Assistant
Thanks for the feedback! Let me provide a complete minimal working example including:
- Minimal
modelpackage structs. - Your
cubejspackage code (simplified). - A
main.gofile that compiles and runs successfully. - Prints the resulting Cube query JSON to confirm the correct time range and filters.
1.
model/model.gopackage model type CubeQueryFilter struct { Member string `json:"Member"` Operator string `json:"Operator"` Values []string `json:"Values"` } type TimeDimension struct { Dimension string `json:"Dimension"` DateRange []string `json:"DateRange"` } type CubeQueryPayload struct { Dimensions []string `json:"Dimensions"` Measures []string `json:"Measures"` Filters []CubeQueryFilter `json:"Filters"` Order map[string]string `json:"Order"` TimeDimensions []TimeDimension `json:"TimeDimensions"` } type CubeQuery struct { Query CubeQueryPayload `json:"Query"` } // ChannelNode example similar to your code type ChannelNode struct { ClientID int WorkspaceID int Delay *int // minutes delay ConditionQuery *string // JSON string of condition query filters (optional) } // ActionNode example similar to your code type ActionNode struct { ConditionQuery string }
2.
cubejs/cubejs.goPlace your provided code, but adjusted to this minimal
modelpackage and improved Build method with 1-min window time range.package cubejs import ( "encoding/json" "fmt" "time" "yourmodule/model" ) type QueryBuilder interface { Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) } type queryBuilder struct{} func NewQueryBuilder() QueryBuilder { return &queryBuilder{} } func (q *queryBuilder) Build(channel model.ChannelNode, action model.ActionNode, offset, limit int, cronStartTime time.Time) (model.CubeQuery, error) { var ( channelFilters []model.CubeQueryFilter actionFilters []model.CubeQueryFilter err error ) if channel.ConditionQuery != nil && *channel.ConditionQuery != "" { channelFilters, err = parseConditionQuery(*channel.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse channel condition query: %w", err) } } if action.ConditionQuery != "" { actionFilters, err = parseConditionQuery(action.ConditionQuery) if err != nil { return model.CubeQuery{}, fmt.Errorf("parse action condition query: %w", err) } } filters := append(channelFilters, actionFilters...) filters = append(filters, []model.CubeQueryFilter{ { Member: "AttributionLogs.clientId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.ClientID)}, }, { Member: "AttributionLogs.workspaceId", Operator: "equals", Values: []string{fmt.Sprintf("%d", channel.WorkspaceID)}, }, }...) delay := 0 if channel.Delay != nil { delay = *channel.Delay } // Calculate the start and end of the 1-minute window for event filtering eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC() eventEndTime := eventStartTime.Add(1 * time.Minute).UTC() startTime := eventStartTime.Format(time.RFC3339) endTime := eventEndTime.Format(time.RFC3339) return model.CubeQuery{ Query: model.CubeQueryPayload{ Dimensions: []string{}, Measures: []string{"AttributionLogs.count"}, Filters: filters, Order: map[string]string{ "AttributionLogs.eventCount": "desc", }, TimeDimensions: []model.TimeDimension{ { Dimension: "AttributionLogs.timestamp", DateRange: []string{startTime, endTime}, }, }, }, }, nil } func parseConditionQuery(raw string) ([]model.CubeQueryFilter, error) { if raw == "" { return nil, nil } var filters []model.CubeQueryFilter err := json.Unmarshal([]byte(raw), &filters) return filters, err }
3.
main.gopackage main import ( "encoding/json" "fmt" "time" "yourmodule/cubejs" // replace with your module path "yourmodule/model" ) func main() { // Delay in minutes - 60 minutes = 1 hour delay := 60 channel := model.ChannelNode{ ClientID: 123, WorkspaceID: 456, Delay: &delay, ConditionQuery: nil, // no channel-level condition filters for this example } // Add to cart event condition filter to match your message actionQuery := `[{"Member":"AttributionLogs.eventName","Operator":"equals","Values":["Add to cart"]}]` action := model.ActionNode{ ConditionQuery: actionQuery, } // Cron time when SMS trigger happens at 11:05 UTC cronTime, err := time.Parse(time.RFC3339, "2024-06-12T11:05:00Z") if err != nil { panic(err) } builder := cubejs.NewQueryBuilder() query, err := builder.Build(channel, action, 0, 100, cronTime) if err != nil { panic(err) } // Print as formatted JSON prettyJSON, err := json.MarshalIndent(query, "", " ") if err != nil { panic(err) } fmt.Println("Generated Cube Query:") fmt.Println(string(prettyJSON)) }
4. Run and check output
If you replace
"yourmodule/cubejs"and"yourmodule/model"with your actual module path(s), save the files and run:go run main.goExpected output:
Generated Cube Query: { "Query": { "Dimensions": [], "Measures": [ "AttributionLogs.count" ], "Filters": [ { "Member": "AttributionLogs.eventName", "Operator": "equals", "Values": [ "Add to cart" ] }, { "Member": "AttributionLogs.clientId", "Operator": "equals", "Values": [ "123" ] }, { "Member": "AttributionLogs.workspaceId", "Operator": "equals", "Values": [ "456" ] } ], "Order": { "AttributionLogs.eventCount": "desc" }, "TimeDimensions": [ { "Dimension": "AttributionLogs.timestamp", "DateRange": [ "2024-06-12T10:05:00Z", "2024-06-12T10:06:00Z" ] } ] } }
Summary
- The
TimeDimensions.DateRangecorresponds exactly to the event minute window 60 minutes before your 11:05 SMS trigger: from 10:05:00 to 10:06:00 UTC. - Filters apply as expected (client, workspace, and the event name "Add to cart").
- You have a working runnable program that prints the constructed query.
If you want, I can help prepare a zipped project or a GitHub repo example too. Just let me know!
- Minimal
- User
can we make time range in this format : "2025-02-27 16:37:00", "2025-02-27 16:37:59"
- Assistant
Yes, you can absolutely format the time range like that instead of RFC3339 — just format your timestamps using
time.Formatwith the layout"2006-01-02 15:04:05"and then adjust the end time's seconds to59(instead of a full 1-minute ahead).
How to do that:
- Format start time as
"YYYY-MM-DD HH:mm:ss"(e.g."2025-02-27 16:37:00"). - Format end time as
"YYYY-MM-DD HH:mm:ss"but setting seconds to59(e.g."2025-02-27 16:37:59").
Adjusting the Build method snippet:
Replace this part in your
Buildmethod:eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC() eventEndTime := eventStartTime.Add(1 * time.Minute).UTC() startTime := eventStartTime.Format(time.RFC3339) endTime := eventEndTime.Format(time.RFC3339)with something like this:
// Calculate start time at beginning of the minute (seconds = 0) eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC() eventStartTime = eventStartTime.Truncate(time.Minute) // Calculate end time with seconds = 59 in same minute eventEndTime := eventStartTime.Add(59 * time.Second) // Format times in "YYYY-MM-DD HH:mm:ss" format := "2006-01-02 15:04:05" startTime := eventStartTime.Format(format) endTime := eventEndTime.Format(format)
Full relevant part:
eventStartTime := cronStartTime.Add(-time.Duration(delay) * time.Minute).UTC() eventStartTime = eventStartTime.Truncate(time.Minute) eventEndTime := eventStartTime.Add(59 * time.Second) format := "2006-01-02 15:04:05" startTime := eventStartTime.Format(format) endTime := eventEndTime.Format(format)
Result example:
If cronStartTime =
"2025-02-27T16:37:00Z"and delay = 0:"DateRange": [ "2025-02-27 16:37:00", "2025-02-27 16:37:59" ]
Note: Make sure your Cube backend supports this datetime string format. Sometimes APIs expect ISO 8601 / RFC3339 timestamps with time zone info, but if your Cube backend is known to accept and parse
"YYYY-MM-DD HH:mm:ss"strings, this is perfectly fine.
If you want, I can provide the full updated
Buildfunction with this format! - Format start time as