Skip to contents

SQL Expression: Time Grouping

Source: R/sql_expressions.R
hsSqlExTimeGroup.Rd

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

Usage

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))