1. Differentiates date and time data types (%Date, %Time, %TimeStamp, %PosixTime)
Key Points
- %Date: Stores date only, logical value is integer days since December 31, 1840
- %Time: Stores time only, logical value is seconds past midnight (0-86399)
- %TimeStamp: Combined date and time in YYYY-MM-DD HH:MM:SS.nnnnnnnnn format
- %PosixTime: Encoded 64-bit timestamp, seconds elapsed from 1970-01-01 00:00:00
- Each type has distinct SqlCategory values: DATE, TIME, TIMESTAMP, and POSIXTS
Detailed Notes
%Date Data Type
The %Date data type stores date-only values with a logical representation as an integer representing the number of days since December 31, 1840 (day 0).
- Storage format: Compact integer representation
- Date range: Supports dates from 0001-01-01 to 9999-12-31
- Display format: Locale-specific formats or ODBC format (YYYY-MM-DD)
- SqlCategory: DATE
%Time Data Type
The %Time data type represents time-of-day values, storing the logical value as the number of seconds past midnight.
- Value range: 0 to 86399 seconds
- Display format: HH:MM:SS
- SqlCategory: TIME
- Use case: Efficient time arithmetic and comparisons
%TimeStamp Data Type
The %TimeStamp data type combines both date and time components into a single value.
- Format: YYYY-MM-DD HH:MM:SS.nnnnnnnnn
- Precision: Up to nine digits of fractional seconds
- Conversion: Logical value obtained from $HOROLOG format using $ZDATETIME(h,3)
- SqlCategory: TIMESTAMP
- Use case: Recording exact moments in time (standard ODBC timestamp format)
%PosixTime Data Type
The %PosixTime data type offers an alternative timestamp representation using Unix epoch encoding.
- Storage: Encoded 64-bit signed integer representing elapsed seconds with microsecond precision
- Epoch: 1970-01-01 00:00:00 (represented as 1152921504606846976)
- Date range: 0001-01-01 to 9999-12-31 (same as other temporal types)
- Precision: Exactly 6 digits of fractional seconds
- SqlCategory: POSIXTS
- Note: Dates before 1970 have negative values
SqlCategory and Type Conversion
Each data type has a distinct SqlCategory classification that controls comparison operations.
- Conversion functions: Use DATE(), TO_TIMESTAMP(), or TO_POSIXTIME() when mixing temporal types in comparisons or indexes
- Schema design impact: Choice of temporal data type affects storage efficiency, query performance, and interoperability with external systems
- Recommendations:
- %Date and %Time: Most compact storage for date-only or time-only values
- %TimeStamp: Human-readable precision timestamps
- %PosixTime: Unix timestamp compatibility and high-precision temporal calculations
Exam Preparation Summary
Critical Concepts to Master:
- Logical Storage Formats: Know that %Date stores days since Dec 31, 1840, %Time stores seconds past midnight, %TimeStamp uses ODBC format, %PosixTime uses encoded Unix timestamp
- SqlCategory Values: Memorize the four distinct categories (DATE, TIME, TIMESTAMP, POSIXTS) and their impact on comparisons
- Precision Differences: %TimeStamp supports up to 9 digits of fractional seconds, %PosixTime encodes exactly 6 digits, %Time has no fractional seconds
- Conversion Functions: Understand when to use DATE(), TO_TIMESTAMP(), TO_POSIXTIME() for type conversion
- Use Case Selection: Know when to choose each type - %Date for dates only, %Time for times only, %TimeStamp for general timestamps, %PosixTime for Unix compatibility
Common Exam Scenarios:
- Selecting the appropriate temporal data type for a given application requirement
- Converting between different temporal formats using SQL functions
- Understanding the range and precision limitations of each type
- Recognizing the encoded nature of %PosixTime values
- Working with $HOROLOG and $ZTIMESTAMP special variables in SQL context
Hands-On Practice Recommendations:
- Create tables with columns of each temporal type and insert sample data
- Practice conversions between types using DATE(), TO_TIMESTAMP(), and TO_POSIXTIME()
- Experiment with fractional seconds precision in %TimeStamp and %PosixTime
- Query temporal data with different display modes (Logical, ODBC, Display)
- Compare performance and storage characteristics of each type
- Work with dates before 1970 to understand %PosixTime negative values