Use a virtual timestamp


Most datasources support virtual timestamps. If you're working with a datasource that doesn't, you won't see the option to create one.

You can add a timestamp column to a table by creating a virtual timestamp. You create a virtual timestamp for a table in the Manage Table Settings modal.

  1. Select the Timestamp field, then select Create virtual timestamp +.
  2. Select a column with values you can turn into a timestamp using functions.
  3. Enter a function for converting the column's values into UTC-format timestamp values. For example, if you store timestamps as the epoch with millisecond precision, your input might be TO_TIMESTAMP({column_value}/1000).
  4. Enter a function for converting UTC-format timestamp values into valid column values, then select Save. For example, your input might be EXTRACT(epoch FROM {timestamp_value}) *1000.


If you already have stored procedures for these transformations, you can use the procedures as inputs if Lightup is able to access them.

Virtual timestamp function examples

The following table lists the functions you would use to transform a column value to and from a timestamp value, if the column values were stored as the epoch with millisecond precision. You can use these as a starting point, adjusting the transformation as needed to accommodate your column values.

AthenaFROM_UNIXTIME({column_value} / 1000)TO_UNIXTIME({timestamp_value}) * 1000
BigQueryTIMESTAMP_MILLIS({column_value})UNIX_SECONDS({timestamp_value}) * 1000
DatabricksCAST(({column_value} / 1000) AS TIMESTAMP)UNIX_TIMESTAMP({timestamp_value})::BIGINT * 1000
GreenplumTO_TIMESTAMP({column_value} / 1000)EXTRACT(epoch FROM {timestamp_value}) * 1000
HiveFROM_UNIXTIME(CAST({column_value} / 1000 AS BIGINT))UNIX_TIMESTAMP({timestamp_value}) * 1000
Microsoft SQLDATEADD(s, {column_value} / 1000, '19700101')CAST(DATEDIFF(s, '19700101', {timestamp_value}) <br><br>AS BIGINT) * 1000
Oracle(timestamp '1970-01-01 00:00:00') + numtodsinterval({column_value} / 1000, 'second')(CAST(SYS_EXTRACT_UTC({timestamp_value}) AS DATE) - DATE '1970-01-01' ) * 86400 * 1000
PostgresTO_TIMESTAMP({column_value} / 1000)EXTRACT(epoch FROM {timestamp_value}) * 1000
RedshiftTIMESTAMP 'epoch' + {column_value} / 1000 * interval '1 second'EXTRACT(epoch FROM {timestamp_value})::bigint * 1000
SnowflakeTO_TIMESTAMP({column_value} / 1000)EXTRACT(epoch FROM {timestamp_value}) * 1000
TeradataTO_TIMESTAMP({column_value} / 1000)CAST(((CAST(CAST({timestamp_value} AS TIMESTAMP) AS DATE AT 'GMT') - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM CAST({timestamp_value} AS TIMESTAMP) AT 'GMT') * 3600) + (EXTRACT(MINUTE FROM CAST({timestamp_value} AS TIMESTAMP) AT 'GMT') * 60) + (EXTRACT(SECOND FROM CAST({timestamp_value} AS TIMESTAMP)))) * 1000 AS BIGINT)