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.

The virtual timestamp settings for a table, displayed in the Manage Table Settings modal.The virtual timestamp settings for a table, displayed in the Manage Table Settings modal.

  1. Select the Timestamp field, then select + Create a virtual column.
  2. In the flyout that appears, 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.

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