Version

    Snowflake Connection

    You can connect to Snowflake in the same way as to any other SQL database using a DB Connection with the bundled Snowflake JDBC driver.

    This section contains information about working with date-time data types that is common to DatabaseReader, DatabaseWriter and SnowflakeBulkWriter.

    Date/Time Data Types in Snowflake

    Working with dates and time zones can be challenging. As a quick rule before going into details, always specify the Format of date data fields in your metadata, see Date and Time Format. Also setting the Session Time Zone on the connection to match your local time zone may help.

    [Tip]Tip
    See SnowflakeDateTime.grf sample graph in the BasicExamples project: File > New > Example... > CloverDX Examples Project

    There are five different Snowflake data types and two time zones (session TZ and local TZ) that affect the results.

    Table 32.1. Snowflake Date/Time Types

    Snowflake typeDescriptionTime zone
    DATEA date without a time component.local
    TIMEWallclock time without a date.
    TIMESTAMP_NTZ Date and time without a time zone. Saved as the wallclock date-time in the session time zone. The time zone itself is not stored. session
    TIMESTAMP_TZ Date and time with a time zone. The session time zone is stored as a part of the value and used for reading.
    TIMESTAMP_LTZ Written in the local time zone, but the time zone is not stored. The current time zone is always used for reading. local

    All of these Snowflake types map to just one data type in CloverDX metadata: date.

    An important property of the date data type is its Format. It provides a hint on how to interpret the data while reading or writing them to the database.

    If the Format contains only a date part, it maps to the DATE type. If it contains only a time part, we consider it a TIME. And if it contains both, we treat it as a TIMESTAMP.

    For example, "yyyy-MM-dd" or "dd/MM/yyyy" are DATE formats, "HH:mm:ss" is a TIME format, and "dd.MM.yyyy HH:mm:ss" is a TIMESTAMP.

    See Date and Time Format for more details.

    Note that regardless of the Format, CloverDX records store the full date and time value. The Format controls how that value is interpreted when reading and writing data.

    Session Time Zone

    This is the time zone of the connection, as configured in Snowflake. It is used for writing TIMESTAMPs.

    See the TIMEZONE parameter in Snowflake documentation.

    You can override the session time zone on the connection in Advanced Properties of JDBC driver, e.g., TIMEZONE=Asia/Singapore.

    Local Time Zone

    This is the client time zone. It is used for reading and writing DATE and TIME columns. It is set to your system time zone by default. But you can override it in your metadata, even for individual fields. See Date and Time Format and Time Zone for details.