timevalue Format

The timevalue parameter represents a date/time in a format understood by the date and time functions.

timevalue can be in any of the following formats:
  1. YYYY-MM-DD
  2. YYYY-MM-DD HH:MM
  3. YYYY-MM-DD HH:MM:SS
  4. YYYY-MM-DD HH:MM:SS.SSS
  5. YYYY-MM-DDTHH:MM
  6. YYYY-MM-DDTHH:MM:SS
  7. YYYY-MM-DDTHH:MM:SS.SSS
  8. HH:MM
  9. HH:MM:SS
  10. HH:MM:SS.SSS
  11. D[D..][.D..]
  12. now

When timevalue is the name of a column in a table, the value of the column is substituted for the timevalue parameter. As per ISO-8601, a date and time can be combined using the literal character T. The T can also be omitted by mutual agreement. Both ways of combining dates and times into a single string are supported.

Formats 2 through 10 can optionally be followed by a timezone indicator. The timezone indicator can have the following formats:
  • [+-]HH:MM
  • Z

Z represents UTC time and is the timezone used to store dates and times in the data grid. If HH:MM is non-zero, it is subtracted from the date and time and is intended to be used to convert to UTC time.

The fractional seconds value SS.SSS can have one or more digits following the decimal point but only the first three digits are considered. Therefore, support for full nanosecond precision of tibDateTime values stored into ActiveSpaces datetime columns must not be expected for queries.

Format 11 is the Julian day number expressed as a long or floating point number. This format can accept any number of digits as required to represent the Julian day number. Format 11 can also be a long which represents Unix time. By default long values are interpreted as Julian days. The unixepoch modifier must follow the column name in the parameters passed to the date and time functions for the value of the column to be interpreted as Unix time. See the section Modifiers.

now is converted into the current date and time. However as each node of the data grid executes the query looking for rows to return, the use of now must be carefully analyzed to ensure the query returns the results you expect.
Note: The SQL variables CURRENT_DATE, CURRENT_TIME, and CURRENT_TIMESTAMP can be used instead of specifying date('now'), time('now'), and datetime('now'), respectively.
Related concepts