📅Date Data Types and Time-Zone Handling in Sprinkle
Overview
Sprinkle standardizes how date and date-time values are stored and presented to ensure consistency across ingestion pipelines and reporting layers.
It is important to distinguish between:
Inbuilt (non-database) connectors
Database connectors
Analytics (Reports)
Time-zone interpretation and epoch conversion apply only to database connectors. Other connectors ingest date values as received.
1. Date Handling During Ingestion
1.1 Inbuilt (Non-Database) Connectors
Different external systems expose date and date-time values in varying formats and time zones. For inbuilt connectors:
Date fields are ingested as provided by the source system
Values are stored in the warehouse in their original string/text format.
No time-zone interpretation or epoch conversion is applied during ingestion.
This ensures fidelity to the source data and avoids implicit time-zone assumptions.
Important: Since values are stored as text, downstream transformation or casting may be required depending on reporting needs.
1.2 Database Connectors
For database connectors, Sprinkle provides a Connector Time-Zone configuration.
This setting determines how date and date-time values in the source database are interpreted during ingestion.
Ingestion Flow for Database Connectors
Date/time values are read from the source database.
The system interprets those values according to the configured Connector Time Zone.
The interpreted value is converted to epoch time (milliseconds).
The epoch value is stored in the Data Warehouse.
Epoch time represents the number of milliseconds since:
January 1, 1970, 00:00:00 UTC
Examples
Connector Time Zone: UTC
Source value: 12/15/2005 7:30:00 AM
Stored epoch value: 1134631800000
Connector Time Zone: IST
Source value: 2025-02-16 04:30:00 PM
Stored epoch value: 1739703600000
Equivalent UTC time: February 16, 2025, 11:00:00 AM
Note: The same date-time string will produce different epoch values if interpreted under different connector time zones.
2. Date Handling in Analytics (Reports)
2.1 Storage Format in the Warehouse
For database connectors: date fields are stored as epoch time (milliseconds).
For other connectors: date fields remain stored as string/text values unless explicitly transformed.
2.2 Automatic Conversion in Reports
For tables created through database ingestion pipelines:
Epoch values are automatically converted to a readable date-time format when properly configured in reports.
Display format: yyyy-mm-dd hh24:mi
Conversion is performed using the Account Time Zone configured in Account Settings.
2.3 When Automatic Conversion Applies
Automatic epoch-to-date conversion applies when:
Reports are created using Models, and the fields are marked as Date Dimensions, or
Reports are created using Tables, and the columns are marked Identify as Date in the column configuration panel.
2.4 When Manual Conversion Is Required
Manual conversion is required when:
Building reports using the SQL Editor
Using custom expressions
Selecting epoch fields as generic dimensions without marking them as date fields
In such cases, users must explicitly convert epoch values using appropriate SQL functions.
For non-database connectors where dates are stored as strings, users may need to:
Cast text values to date/time types
Apply parsing logic
Handle time-zone adjustments explicitly
3. Account Time-Zone Behavior in Reports
The displayed date-time value depends on the Account Time Zone setting.
For epoch-based fields (database connectors), the same stored value may render differently depending on the account time zone.
Examples
Account Time Zone: IST
Epoch value: 1739703600000
Displayed as: 2025-02-16 16:30
Account Time Zone: UTC
Epoch value: 1134631800000
Displayed as: 2005-12-15 07:30
4. Key Principles
Time-zone interpretation and epoch conversion occur only for database connectors.
For database connectors, date values are stored as epoch time (milliseconds).
The Connector Time Zone determines how source database values are interpreted during ingestion.
The Account Time Zone determines how epoch values are displayed in reports.
For all other connectors, date fields are ingested as-is and stored in string/text format.
Automatic conversion in reports works only when fields are correctly defined as date dimensions.
SQL-based reports require explicit conversion logic where applicable.
5. Best Practices
Always verify the Connector Time Zone when configuring database ingestion.
Confirm the Account Time Zone aligns with business reporting requirements.
Ensure epoch fields are properly marked as Date Dimensions in models.
For non-database connectors, validate and standardize date formats during transformation.
Use explicit parsing and conversion functions when building SQL-based reports.
For further clarification on date standardization or time-zone behavior, please contact the Sprinkle support team.
Last updated