Skip to content
  • There are no suggestions because the search field is empty.

Best Practice: Cast datetimes before outputting to a database

Prevent failed inserts and inconsistent timestamp storage by ensuring the payload contains DateTime values (not strings) before writing to a database

Overview

When writing timestamps to a SQL database through Intelligence Hub, ensure datetime values are written as a DateTime type, not as strings. Sending timestamps as string values (even ISO 8601) can cause type mismatches between the event payload and the database column, which may lead to failed inserts or unexpected results.

By casting or mapping timestamp values to DateTime (preferably in an instance attribute), the Intelligence Hub can bind the correct JDBC parameter type when writing to the database, allowing the driver to store timestamps accurately and apply the expected time zone handling.

Problem

When inserting data into a database, the Intelligence Hub determines how to pass each value to the JDBC driver based on the value’s type.

  • If a timestamp is passed as a string, the driver treats it as text, even if it looks like a valid ISO 8601 timestamp.
  • If a timestamp is passed as a DateTime, the driver interprets it correctly as a timestamp and applies the correct timezone logic (UTC by default).

Recommended Practice

Always cast datetime values to a DateTime type before writing to a database.

You can do this in several ways:

  • In a Model attribute (Best Way):
    Set the attribute’s model type to DateTime. Use the model in an instance or modeling stage in a pipeline.
  • In an Expression:
    Use the Date() method or reference the internal system datetime.
    Example:
    • Date(timestamp_string_variable);

Using an expression with Date() relies on how the datetime string is formatted and how the underlying database/driver interprets it. Not all string formats are supported equally. Behavior can vary between databases (and even between versions or JDBC drivers) in terms of accepted formats, timezone rules, and fractional-second precision. If Date() fails to parse the value or the database stores an unexpected result, verify that:

  • The string is in a standard, unambiguous format (such as ISO 8601: YYYY-MM-DDThh:mm:ss.sssZ), and

  • The target database table column type and JDBC driver both support that format.

When in doubt, prefer defining the attribute as DateTime in the Model and ensuring the source produces a consistent, well-formed datetime string before casting.