How would you implement Date Time based filters

[rugwirobaker]

The way to built queries is very interesting. I wonder how you would approach this if you had a DateTime based filter like:

from : "2000-01-01"
to: "2000-01-02"

And somehow you had to build a query with BETWEEN "from" and "to" as one condition among your others in your where clause.
This is in reference to the magic you did here:

wtf/sqlite/dial_membership.go

Line 144 in aab552f

where, args := []string{"1 = 1"}, []interface{}{}

[benbjohnson]

Good question. I usually separate out the two filters. So if it's a filtered on a Created timestamp then I would have CreatedAfter and CreatedBefore fields on my filter:

type WidgetFilter struct {
	CreatedAfter  *time.Time
	CreatedBefore *time.Time
}

Then in the SQL construction it would look something like:

if v := filter.CreatedAfter; v != nil {
	where = append(where, "created_at >= ?")
	args = append(args, *v)
}

if v := filter.CreatedBefore; v != nil {
	where = append(where, "created_at < ?")
	args = append(args, *v)
}

[rugwirobaker]

I suppose this doesn't work for database drivers that use $1 placeholder instead of ?, since you would start jumping from $1 to $3 if the arg corresponding to $2 where to be nil.

[benbjohnson]

That's a good point. You could use a helper to track the positional arg. e.g.

// posArg returns a SQL positional argument and increments the position.
func posArg(pos *int) string {
	(*pos)++
	return fmt.Sprintf("$%d", *pos)
}