Create a virtual timestamp

If you find you have no timestamp column available but you know how to compute your timestamp using a suitable column, you can create a virtual timestamp.

  1. Select the timestamp setting, and then click Create Virtual Timestamp +.
  2. In the form 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.

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