SQL Expression: Time Grouping

Source: R/sql_expressions.R

Generates SQL code for grouping timestamps by years, months or days

hsSqlExTimeGroup(tbl, tsField, interval, cond = "TRUE")

Arguments

tbl

name of the table
tsField

name of the table field containing the timestamp
interval

specifies the time period to group by ("y": years, "m": months, "d": days)
cond

additional condition in SQL syntax

Value

Returns SQL code for grouping timestamps by years, months or days

See also

hsSqlExTimeCond

Examples

## Show SQL query that gets the number of datasets per 
## day ("d") considering the condition "Q > 0"
hsSqlExTimeGroup("myTable", "myTimestamp", "d", "Q > 0")
#> [1] "SELECT DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) AS myInterval, Count(*) AS myCount FROM (SELECT myTimestamp AS hsTS FROM [myTable] WHERE Q > 0) GROUP BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) ORDER BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS))"
  
## Output (reformatted):  
## SELECT DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) 
##   AS myInterval, Count(*) AS myCount 
## FROM (
##     SELECT myTimestamp AS hsTS FROM myTable WHERE Q > 0
## ) 
## GROUP BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS)) 
## ORDER BY DateSerial(Year(hsTS), Month(hsTS), Day(hsTS))