ClickHouse Puts the Time Zone in the Type, Not the Value

// src/DataTypes/DataTypeDateTime.h:10
/** DateTime stores time as unix timestamp.
  * The value itself is independent of time zone.
  *
  * In binary format it is represented as unix timestamp.
  * In text format it is serialized to and parsed from YYYY-MM-DD hh:mm:ss format.
  * The text format is dependent of time zone.
  *
  * To cast from/to text format, time zone may be specified explicitly or implicit time zone may be used.
  *
  * Time zone may be specified explicitly as type parameter, example: DateTime('Pacific/Pitcairn').
  * As it does not affect the internal representation of values,
  *  all types with different time zones are equivalent and may be used interchangingly.
  * Time zone only affects parsing and displaying in text formats.
  *
  * If time zone is not specified (example: DateTime without parameter),
  * then `session_timezone` setting value is used.
  * If `session_timezone` is not set (or empty string), server default time zone is used.
  * Default time zone is server time zone, if server is doing transformations
  *  and if client is doing transformations, unless 'use_client_time_zone' setting is passed to client;
  * Server time zone is the time zone specified in 'timezone' parameter in configuration file,
  *  or system time zone at the moment of server startup.
  */

// src/DataTypes/DataTypeDateTime.cpp:33
bool DataTypeDateTime::equals(const IDataType & rhs) const
{
    /// DateTime with different timezones are equal, because:
    /// "all types with different time zones are equivalent and may be used interchangingly."
    return typeid(rhs) == typeid(*this);
}

// src/DataTypes/DataTypeDateTime64.cpp:50
void DataTypeDateTime64::updateHashImpl(SipHash & hash) const
{
    Base::updateHashImpl(hash);
    /// Do not include timezone in hash: equals() considers types with different
    /// timezones as equal (only scale matters), and hash must be consistent with equality.
}

bool DataTypeDateTime64::equals(const IDataType & rhs) const
{
    if (const auto * ptype = typeid_cast<const DataTypeDateTime64 *>(&rhs))
        return this->scale == ptype->getScale();
    return false;
}

// src/DataTypes/TimezoneMixin.h:17
class TimezoneMixin
{
public:
    TimezoneMixin(const TimezoneMixin &) = default;

    explicit TimezoneMixin(std::string_view time_zone_name = "")
        : has_explicit_time_zone(!time_zone_name.empty())
        , time_zone(DateLUT::instance(time_zone_name))
    {
    }

    const DateLUTImpl & getTimeZone() const { return time_zone; }
    bool hasExplicitTimeZone() const { return has_explicit_time_zone; }

protected:
    /// true if time zone name was provided in data type parameters, false if it's using default time zone.
    bool has_explicit_time_zone;

    const DateLUTImpl & time_zone;
};

// src/DataTypes/TimezoneMixin.h:10
  * Note that the UTC time zone is deliberately not kept here, even though the `best_effort` date/time parsers and
  * ISO output need it. Constructing a `DateLUTImpl` walks ~146k days through cctz, and the data types
  * (`DataTypeDateTime`, `DataTypeDateTime64`) are constructed just to name a column type - e.g. when building the
  * schemas of the system tables at startup - without ever touching UTC. Resolving UTC here made every such
  * construction build a second, always-unused lookup table. The serializations that do need it declare their own
  * `utc_time_zone` member instead, so the cost is paid once per formatter.

// src/Common/DateLUTImpl.h:21
#define DATE_SECONDS_PER_DAY 86400 /// Number of seconds in a day, 60 * 60 * 24

#define DATE_LUT_MIN_YEAR 1900 /// 1900 since majority of financial organizations consider 1900 as an initial year.
#define DATE_LUT_MAX_YEAR 2299 /// Last year covered by the lookup table (complete)
#define DATE_LUT_YEARS (1 + DATE_LUT_MAX_YEAR - DATE_LUT_MIN_YEAR) /// Number of years in lookup table

/// Years outside the lookup table are handled with cctz. The overall representable range is bounded by the
/// 4-digit year used when formatting, i.e. [0000, 9999].
#define DATE_LUT_MIN_REPRESENTABLE_YEAR 0
#define DATE_LUT_MAX_REPRESENTABLE_YEAR 9999

#define DATE_LUT_SIZE 0x23AB1

// src/Common/DateLUTImpl.h:187
    /// The order of fields matters for alignment and sizeof.
    struct Values
    {
        /// Time at beginning of the day.
        Time date;

        /// Properties of the day.
        UInt16 year;
        UInt8 month;
        UInt8 day_of_month;
        UInt8 day_of_week;

        /// Total number of days in current month. Actually we can use separate table that is independent of time zone.
        /// But due to alignment, this field is totally zero cost.
        UInt8 days_in_month;

        /// For days, when offset from UTC was changed due to daylight saving time or permanent change, following values could be non zero.
        /// All in OffsetChangeFactor (15 minute) intervals.
        Int8 amount_of_offset_change_value; /// Usually -4 or 4, but look at Lord Howe Island. Multiply by OffsetChangeFactor
        UInt8 time_at_offset_change_value; /// In seconds from beginning of the day. Multiply by OffsetChangeFactor

        Int32 amount_of_offset_change() const /// NOLINT
        {
            return static_cast<Int32>(amount_of_offset_change_value) * OffsetChangeFactor;
        }

        UInt32 time_at_offset_change() const /// NOLINT
        {
            return static_cast<UInt32>(time_at_offset_change_value) * OffsetChangeFactor;
        }

        /// Since most of the modern timezones have a DST change aligned to 15 minutes, to save as much space as possible inside Value,
        /// we are dividing any offset change related value by this factor before setting it to Value,
        /// hence it has to be explicitly multiplied back by this factor before being used.
        static constexpr UInt16 OffsetChangeFactor = 900;
    };

    static_assert(sizeof(Values) == 16);

// src/Common/DateLUTImpl.h:227
    /// Offset to epoch in days (ExtendedDayNum) of the first day in LUT.
    /// "epoch" is the Unix Epoch (starts at unix timestamp zero)
    static constexpr UInt32 daynum_offset_epoch = 25567;
    static_assert(daynum_offset_epoch == (1970 - DATE_LUT_MIN_YEAR) * 365 + (1970 - DATE_LUT_MIN_YEAR / 4 * 4) / 4);

    /// Lookup table is indexed by LUTIndex.
    /// Day nums are the same in all time zones. 1970-01-01 is 0 and so on.
    /// Table is relatively large, so better not to place the object on stack.
    /// In comparison to std::vector, plain array is cheaper by one indirection.
    Values lut[DATE_LUT_SIZE + 1]; // NOLINT(cppcoreguidelines-pro-type-member-init,hicpp-member-init) - fully assigned in constructor

    /// Start of the first day that begins at or after 1970-01-01 00:00:00 UTC. Results for unsigned `Date` and
    /// `DateTime` are clamped to it, because those types cannot represent earlier days.
    Time first_non_negative_date;

    Time saturated(Time t) const { return std::max(t, first_non_negative_date); }

    /// Year number after DATE_LUT_MIN_YEAR -> LUTIndex in lut for start of year.
    LUTIndex years_lut[DATE_LUT_YEARS];

    /// Year number after DATE_LUT_MIN_YEAR * month number starting at zero -> day num for first day of month
    LUTIndex years_months_lut[DATE_LUT_YEARS * 12];

// src/Common/DateLUTImpl.cpp:208
    /// The calendar is walked with plain counters; `cctz` is consulted only at offset transitions.
    int year = DATE_LUT_MIN_YEAR;
    int month = 1;
    int day_of_month = 1;
    int days_in_month = daysInCivilMonth(year, month);
    UInt8 day_of_week = getDayOfWeek(lut_start);

    Int64 next_transition_index = 0;

    /// Fill the lookup table:
    /// Adjustments only occur at the dates of transitions. We save next_transition_index and add 24h to the
    /// previous value until we reach it. Then we do the adjustment and get the new next_transition_index.
    UInt32 i = 0;
    do
    {
        Values & values = lut[i];

        values.time_at_offset_change_value = 0;
        values.amount_of_offset_change_value = 0;

        if (i >= next_transition_index)
        {
            const cctz::civil_day date = lut_start + i;
            const cctz::time_point<cctz::seconds> start_of_day_time_point = lookupTz(tz, date);
            start_of_day = std::chrono::system_clock::to_time_t(start_of_day_time_point);

            /// If UTC offset was changed this day.
            /// Change in time zone without transition is possible, e.g. Moscow 1991 Sun, 31 Mar, 02:00 MSK to EEST
            cctz::time_zone::civil_transition transition{};
            const bool has_next_transition = tz.next_transition(start_of_day_time_point - std::chrono::seconds(1), &transition);
            if (has_next_transition
                && (cctz::civil_day(transition.from) == date || cctz::civil_day(transition.to) == date)
                && transition.from != transition.to)
            {
                values.time_at_offset_change_value = static_cast<UInt8>((transition.from - cctz::civil_second(date)) / Values::OffsetChangeFactor);
                values.amount_of_offset_change_value = static_cast<Int8>((transition.to - transition.from) / Values::OffsetChangeFactor);

                /// We don't support too large changes.
                if (values.amount_of_offset_change_value > 24 * 4)
                    values.amount_of_offset_change_value = 24 * 4;
                else if (values.amount_of_offset_change_value < -24 * 4)
                    values.amount_of_offset_change_value = -24 * 4;

                /// We don't support cases when time change results in switching to previous day.
                /// Shift the point of time change later.
                if (values.time_at_offset_change_value + values.amount_of_offset_change_value < 0)
                    values.time_at_offset_change_value = -values.amount_of_offset_change_value;
            }

            /// After the last transition (all the time for UTC) the offset never changes, so stop looking up every day.
            next_transition_index = has_next_transition
                ? std::min(cctz::civil_day(transition.to), cctz::civil_day(transition.from)) - lut_start
                : std::numeric_limits<Int64>::max();
        }

// src/Common/DateLUTImpl.cpp:340
    /// Fill lookup table for years and months.
    size_t year_months_lut_index = 0;
    unsigned first_day_of_last_month = 0;

    for (unsigned day = 0; day < DATE_LUT_SIZE; ++day)
    {
        const Values & values = lut[day];

        if (values.day_of_month == 1)
        {
            if (values.month == 1)
                years_lut[values.year - DATE_LUT_MIN_YEAR] = day;

            year_months_lut_index = (values.year - DATE_LUT_MIN_YEAR) * 12 + values.month - 1;
            years_months_lut[year_months_lut_index] = day;
            first_day_of_last_month = day;
        }
    }

// src/Common/DateLUTImpl.h:372
    LUTIndex findIndex(Time t) const
    {
        /// First guess.
        Time guess = (t / 86400) + daynum_offset_epoch;

        /// For negative Time the integer division was rounded up, so the guess is offset by one.
        if (unlikely(t < 0))
            --guess;

        if (guess < 0)
            return LUTIndex(0);
        if (guess >= DATE_LUT_SIZE)
            return LUTIndex(DATE_LUT_SIZE - 1);

        /// UTC offset is from -12 to +14 in all known time zones. This requires checking only three indices.

        if (t >= lut[guess].date)
        {
            if (guess + 1 >= DATE_LUT_SIZE || t < lut[guess + 1].date)
                return LUTIndex(static_cast<unsigned>(guess));

            return LUTIndex(static_cast<unsigned>(guess) + 1);
        }

        return LUTIndex(guess ? static_cast<unsigned>(guess) - 1 : 0);
    }

// src/Common/DateLUTImpl.h:598
    template <typename DateOrTime>
    auto toDayNum(DateOrTime v) const
    {
        if constexpr (std::is_unsigned_v<DateOrTime> || std::is_same_v<DateOrTime, DayNum>)
            return DayNum{static_cast<DayNum::UnderlyingType>(saturateMinus(toLUTIndex(v).toUnderType(), daynum_offset_epoch))};
        else
        {
            if constexpr (may_be_out_of_lut_range<DateOrTime>)
                if (unlikely(isOutOfLUTRange(v)))
                    return ExtendedDayNum{static_cast<ExtendedDayNum::UnderlyingType>(outOfRangeDayIndex(v) - daynum_offset_epoch)};
            return ExtendedDayNum{static_cast<ExtendedDayNum::UnderlyingType>(toLUTIndex(v).toUnderType() - daynum_offset_epoch)};
        }
    }

// src/Common/DateLUTImpl.h:350
    /// The Gregorian (and ISO-week) calendar repeats exactly every 400 years == DATE_LUT_SIZE days, and week
    /// numbering is timezone-independent. So for out-of-range day numbers we shift into the LUT range by whole
    /// 400-year cycles, reuse the in-range logic, and adjust the resulting year by 400 * (number of cycles).
    /// Returns the in-range day number; `cycles` receives the number of cycles added (negative if subtracted).
    static constexpr Int64 days_in_400_years = DATE_LUT_SIZE;
    ExtendedDayNum shiftIntoLUTRange(ExtendedDayNum d, Int32 & cycles) const
    {
        Int64 index = static_cast<Int64>(d.toUnderType()) + daynum_offset_epoch;
        Int64 k = 0;
        if (index < 0)
            k = (days_in_400_years - 1 - index) / days_in_400_years;
        else if (index >= days_in_400_years)
            k = -(index / days_in_400_years);
        index += k * days_in_400_years;
        cycles = static_cast<Int32>(k);
        return dayNumOfDayIndex(index);
    }

// src/Common/DateLUTImpl.h:885
    unsigned toHour(Time t) const
    {
        if (unlikely(isOutOfLUTRange(t)))
            return static_cast<unsigned>(toDateTimeComponentsOutOfRange(t).time.hour);

        if (t >= 0 && offset_is_fixed_during_epoch)
            return static_cast<unsigned>(((t + hour_of_day_offset_addend) / 3600) % 24);

        const LUTIndex index = findIndexInRange(t);

        Time time = t - lut[index].date;

        if (time >= lut[index].time_at_offset_change())
            time += lut[index].amount_of_offset_change();

        unsigned res = static_cast<unsigned>(time / 3600);

        /// In case time was changed backwards at the start of next day, we will repeat the hour 23.
        return res <= 23 ? res : 23;
    }

// src/Common/DateLUTImpl.h:930
    unsigned toSecond(Time t) const
    {
        /// Checked before the fast path: the "whole number of minutes" property holds during the epoch,
        /// but historical (pre-1900) offsets can have a sub-minute component (e.g. Moscow's +2:30:17 LMT).
        if (unlikely(isOutOfLUTRange(t)))
            return static_cast<unsigned>(toDateTimeComponentsOutOfRange(t).time.second);

        /// Only from the epoch onward: before it the offset may have a sub-minute component (see the
        /// flag), and `t % 60` would then answer the second of the UTC minute, not of the local one.
        if (t >= 0 && offset_is_whole_number_of_minutes_during_epoch) [[likely]]
            return static_cast<unsigned>(t % 60);

        LUTIndex index = findIndexInRange(t);
        Time time = t - lut[index].date;

        if (time >= lut[index].time_at_offset_change())
            time += lut[index].amount_of_offset_change();

        return time % 60;
    }

// src/Common/DateLUT.cpp:248
const DateLUTImpl & DateLUT::getImplementation(std::string_view time_zone) const
{
    std::lock_guard lock(mutex);

    auto [it, inserted] = impls.emplace(time_zone, nullptr);
    if (inserted)
    {
        /// Lives as long as the process, not the query that first named this time zone.
        MemoryTrackerBlockerInThread not_charged_to_the_query;

        try
        {
            it->second = std::unique_ptr<DateLUTImpl>(new DateLUTImpl(time_zone));
        }
        catch (...)
        {
            /// `DateLUTImpl` construction throws for an unknown time zone. Erase the just-inserted
            /// empty slot; otherwise a stream of distinct invalid time zone names (which can come from
            /// untrusted input, e.g. binary type decoding or `toDateTime(x, '<garbage>')`) would grow
            /// this cache without bound, since entries are never evicted.
            impls.erase(it);
            throw;
        }
    }

    return *it->second;
}

// src/Common/DateLUTImpl.cpp:79
bool DateLUTImpl::isSupportedTimeZoneName(std::string_view time_zone_name)
{
    /// `cctz` resolves far more names than the time zone database has, and every name that gets loaded
    /// permanently costs ~4.6 MiB in the `DateLUT` cache, which never evicts anything because callers
    /// keep bare references into it. The names come from untrusted input - `toDateTime(x, '<name>')`,
    /// the `session_timezone` setting, binary type decoding, the time zone of an Arrow or ORC timestamp
    /// column - so accepting anything beyond the database lets one-line queries make the server
    /// allocate memory that it never gives back. On top of the database, `cctz` accepts:

// src/Common/DateLUTImpl.cpp:112
    /// The `Fixed/` family cannot be rejected outright, because reading an Arrow or ORC timestamp
    /// column with a fixed-offset time zone deliberately produces such a name. Accept only the offsets
    /// a time zone can actually have: a whole number of quarters of an hour, no further from UTC than
    /// 14 hours. That covers every offset in the time zone database - the last one that was not a
    /// multiple of 15 minutes ended in 1972 - and leaves 114 such names (113 offsets, of which zero is
    /// spelled both `+00:00:00` and `-00:00:00`), so together with the 598 names of the database the
    /// cache can hold at most 712 entries.

// src/Common/DateLUT.h:35
    /// Return singleton DateLUTImpl for the server time zone.
    /// It may be set using 'timezone' server setting.
    static ALWAYS_INLINE const DateLUTImpl & serverTimezoneInstance()
    {
        const auto & date_lut = getInstance();
        return *date_lut.default_impl.load(std::memory_order_acquire);
    }

// src/Common/DateLUT.cpp:200
const DateLUTImpl & DateLUT::instance()
{
    const auto & date_lut = getInstance();

    std::optional<std::string> timezone_from_context;
    if (DB::CurrentThread::isInitialized())
    {
        const DB::ContextPtr query_context = DB::CurrentThread::get().tryGetQueryContext();
        if (query_context)
            timezone_from_context.emplace(query_context->getSettingsRef()[DB::Setting::session_timezone]);
    }

    if (!timezone_from_context.has_value())
    {
        /// On the server side, timezone is passed in query_context,
        /// but on CH-client side we have no query context,
        /// and each time we modify client's global context
        const DB::ContextPtr global_context = DB::Context::getGlobalContextInstance();
        if (global_context)
            timezone_from_context.emplace(global_context->getSettingsRef()[DB::Setting::session_timezone]);
    }

    if (timezone_from_context.has_value() && !timezone_from_context->empty())
        return date_lut.getImplementation(*timezone_from_context);

    return serverTimezoneInstance();
}

// src/DataTypes/DataTypeDateTime.cpp:40
SerializationPtr DataTypeDateTime::doGetSerialization(const SerializationInfoSettings &) const
{
    if (!has_explicit_time_zone)
    {
        /// When no explicit timezone, resolve the effective timezone (respects session_timezone).
        /// This is called once per formatter (not per row), so the cost of DateLUT::instance() is negligible.
        const auto & effective_tz = DateLUT::instance();
        if (&effective_tz != &time_zone)
        {
            TimezoneMixin overridden(effective_tz.getTimeZone());
            return SerializationDateTime::create(overridden);
        }
    }
    return SerializationDateTime::create(*this);
}

// src/DataTypes/Serializations/SerializationDateTime.cpp:116
void SerializationDateTime::serializeText(const IColumn & column, size_t row_num, WriteBuffer & ostr, const FormatSettings & settings) const
{
    auto value = assert_cast<const ColumnType &>(column).getData()[row_num];
    switch (settings.date_time_output_format)
    {
        case FormatSettings::DateTimeOutputFormat::Simple:
            writeDateTimeText(value, ostr, time_zone);
            return;
        case FormatSettings::DateTimeOutputFormat::UnixTimestamp:
            writeIntText(value, ostr);
            return;
        case FormatSettings::DateTimeOutputFormat::ISO:
            writeDateTimeTextISO(value, ostr, utc_time_zone);
            return;
    }

// src/Functions/toTimezone.cpp:29
class ExecutableFunctionToTimeZone final : public IExecutableFunction
{
public:
    explicit ExecutableFunctionToTimeZone() = default;

    String getName() const override { return "toTimezone"; }

    ColumnPtr executeImpl(const ColumnsWithTypeAndName & arguments, const DataTypePtr & /*result_type*/, size_t /*input_rows_count*/) const override
    {
        return arguments[0].column;
    }
};

// src/Functions/toTimezone.cpp:104
    DataTypePtr getReturnTypeImpl(const ColumnsWithTypeAndName & arguments) const override
    {
        if (arguments.size() != 2)
            throw Exception(ErrorCodes::NUMBER_OF_ARGUMENTS_DOESNT_MATCH,
                "Number of arguments for function {} doesn't match: passed {}, should be 2",
                getName(), arguments.size());

        const auto which_type = WhichDataType(arguments[0].type);
        if (!which_type.isDateTime() && !which_type.isDateTime64())
            throw Exception(ErrorCodes::ILLEGAL_TYPE_OF_ARGUMENT, "Illegal type {} of argument of function {}. "
                "Should be DateTime or DateTime64", arguments[0].type->getName(), getName());

        String time_zone_name = extractTimeZoneNameFromFunctionArguments(arguments, 1, 0, allow_nonconst_timezone_arguments);

        if (which_type.isDateTime())
            return std::make_shared<DataTypeDateTime>(time_zone_name);

        const auto * date_time64 = assert_cast<const DataTypeDateTime64 *>(arguments[0].type.get());
        return std::make_shared<DataTypeDateTime64>(date_time64->getScale(), time_zone_name);
    }

// src/Functions/toTimezone.cpp:159
SELECT toDateTime('2019-01-01 00:00:00', 'UTC') AS time_utc,
toTypeName(time_utc) AS type_utc,
toInt32(time_utc) AS int32utc,
toTimezone(time_utc, 'Asia/Yekaterinburg') AS time_yekat,
toTypeName(time_yekat) AS type_yekat,
toInt32(time_yekat) AS int32yekat,
toTimezone(time_utc, 'US/Samoa') AS time_samoa,
toTypeName(time_samoa) AS type_samoa,
toInt32(time_samoa) AS int32samoa
FORMAT Vertical;

// src/Functions/toTimezone.cpp:171
Row 1:
──────
time_utc:   2019-01-01 00:00:00
type_utc:   DateTime('UTC')
int32utc:   1546300800
time_yekat: 2019-01-01 05:00:00
type_yekat: DateTime('Asia/Yekaterinburg')
int32yekat: 1546300800
time_samoa: 2018-12-31 13:00:00
type_samoa: DateTime('US/Samoa')
int32samoa: 1546300800

// src/DataTypes/DataTypeDateTime64.cpp:20
static constexpr UInt32 max_scale = 9;

DataTypeDateTime64::DataTypeDateTime64(UInt32 scale_, std::string_view time_zone_name)
    : DataTypeDecimalBase<DateTime64>(DecimalUtils::max_precision<DateTime64>, scale_),
      TimezoneMixin(time_zone_name)
{
    if (scale > max_scale)
        throw Exception(ErrorCodes::ARGUMENT_OUT_OF_BOUND, "Scale {} is too large for DateTime64. "
            "Maximum is up to nanoseconds (9).", std::to_string(scale));
}