Convert_timestamps Transform

The convert_timestamps transform converts strings representing timestamps into timestamp objects in a specified timezone. It can also be used to convert columns that already contain timestamps to the timezone.

Example:

If we want to convert the timestamps in the sf_caltrain_times column of each record to Pacific Standard Time, our configuration will look something like this:

{
  "transforms": [
    {
      "transform_name": "Convert record timestamp",
      "transform_type": "convert_timestamps",
      "filter_stream": [
        "*"
      ],
      "field_names": [
        "sf_caltrain_times"
      ],
      "timezone": "America/Los_Angeles"
    }
  ]
}

Configuration:

Required and optional properties that can be configured for a convert_timestamps transform.

• transform_name: Unique name for the transform.
• transform_type: Type of transform to apply. Should be convert_timestamps.
• filter_stream: List of data streams to transform. Each stream can either be * (all) or asset:stream.
• field_names: List of titles of text columns to convert to timestamps.
• timezone: Local timezone of the timestamps to convert. The timezone name should be in Olson timezone format, eg. Asia/Ho_Chi_Minh.
• format: Describes the timestamp format with the notation from strftime.org. For example, ‘%Y-%m-%d %H:%M’ is the format of ‘2018-01-26 17:21’. If the timestamp format is not set, the transform will attempt to guess the data format.
• unit: Specify the unit of time when converting an epoch time value into a datetime. For example:

>> pd.to_datetime(1490195805, unit='s')
Timestamp('2017-03-22 15:16:45')

Allowed values are: D, s, ms, us, ns. Please note that you cannot specify both unit and format.

Note:

Usually the transform is configured for a specific timestamp format:

{
    "transform_name": "My Time Transform",
    "transform_type": "convert_timestamps",
    "filter_stream": ["*"],
    "field_names": ["timestamp"],
    "format": "%Y-%m-%d %H:%M:%S",
    "timezone": "Asia/Tokyo"
}

However if the format is omitted then it will be automatically guessed:

{
    "transform_name": "My Time Transform",
    "transform_type": "convert_timestamps",
    "filter_stream": ["*"],
    "field_names": ["timestamp"],
    "timezone": "Asia/Tokyo"
}

To convert UNIX Epoch time values to datetime, specify the units of time of the epoch values with unit.

{
    "transform_name": "My Unix Timestamp Transform",
    "transform_type": "convert_timestamps",
    "filter_stream": ["*"],
    "field_names": ["epoch_time"],
    "timezone": "Asia/Tokyo",
    "unit": "s"
}

In general it’s safer to set the format, since otherwise the transform will have to guess which part of the timestamp represents months and which represents days.