ROUNDTIME
- Juliane Wetzel
Syntax
ROUNDTIME(<date>;<string, using a time constant>)
Description
Rounds a date field down to the nearest (s)econd, (m)inute, (h)our, or (d)ay set by a time constant.
Time constants for ROUNDTIME:
| Time unit | Constant |
|---|---|
| Day | d |
| Hour | h |
| Minute | m |
| Second | s |
Limitation
This function can't be applied to an empty value. If a column contains empty values, consider adding a check for this, e.g.
IF(ISBLANK(#DateColumn); null; ROUNDTIME(#DateColumn; "1d"))
Examples
| Date | Time constant | ROUNDTIME returns |
|---|---|---|
| Jan 12, 2013 08:33:12 | 7d | Jan 7, 2013 12:00:00 AM |
| Jan 12, 2013 08:33:12 | 3h | Jan 12, 2013 06:00:00 AM |
| Jan 12, 2013 08:33:12 | 10m | Jan 12, 2013 08:30:00 AM |
| Jan 12, 2013 08:33:12 | 5s | Jan 12, 2013 08:33:10 AM |
Datameer X always uses the following format:
Three character month abbreviation (space) two digit day (comma)(space) four digit year (space) two digit hour (colon) two digit minute (colon) two digit second (space) two character meridiem notation.
(e.g., 12.01.2012 gets transferred to Jan 12, 2012 12:00:00 AM)
When selecting a pattern, you select the way the data gets parsed (your input format, this can be connected to your systems representation of dates and times).