Date and Time Functions

ActiveSpaces provides support for several date and time functions which can be used in queries.

The date and time functions return either a string or a double. For information about running queries which reference a datetime column, see Querying tibDateTime Columns. The following is the list of date and time functions supported.
Date and Time Function Returns
date(timevalue[, modifier[, modifier, …]]) The date as a string in the format YYYY-MM-DD
time(timevalue[, modifier[, modifier, …]]) The time as a string in the format HH:MM:SS
datetime(timevalue[, modifier[, modifier, …]]) The date and time as a string in the format YYYY-MM-DD HH:MM:SS
julianday(timevalue[, modifier[, modifier, …]]) The number of days since noon in Greenwich on November 24, 4714 B.C as a double.
The date and time functions can act on the values of columns which have been defined as one of the following data types: string, double, long, and datetime. The following table shows how each of the data types represent date and time:
Data Type Representation of Date and Time
string ISO_8601 strings. For example, "YYYY-MM-DD HH:MM:SS.SSS"
double Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar
long (default) as Julian day numbers. See type double in the earlier row.
long Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC
datetime two 64-bit integers; one for the number of seconds since January 1, 1970 (Unix epoch), and one for the number of nanoseconds after the time that the sec component denotes.
By default, a column of type long is treated as a Julian day number. A column of type long can also be used to hold Unix time values (e.g. the number of seconds since 1970-01-01 00:00:00 UTC). When using a date and time function on a column of type long that holds Unix time values, the ‘unixepoch’ modifier must follow the column name in the parameters passed to the function otherwise the column value is interpreted as a Julian day. See Modifiers.
The following are examples of using the date 2016-12-24 00:00:00 as values in the different column types:
  • string - "2016-12-24T00:00:00". For details, see timevalue Format.
  • double - 2457746.50000
  • long - 1482566400 (Unix time)
  • datetime - [1482566400, 0]
The date and time functions can be used in the WHERE clause of SELECT statements and in the select list of SELECT statements. For example, 
SELECT * FROM mytable WHERE date(dtmcol)='2016-12-24'
SELECT date(dtmcol) FROM mytable
Related concepts