Date/Time Functions

timezone

Returns the timezone of the current session, i.e. the value of setting session_timezone. If the function is executed in the context of a distributed table, then it generates a normal column with values relevant to each shard, otherwise it produces a constant value.

timeZone()

Alias

   `timezone`.

Returned value

• Timezone.
• Type: String.

Example

SELECT timezone()

┌─timezone()─────┐
│ America/Denver │
└────────────────┘

makeDate

Creates a Date from a year, month and day argument

makeDate(year, month, day);

Alias

MAKEDATE(year, month, day);

Arguments

• year — Year. Integer, Float or Decimal.
• month — Month. Integer, Float or Decimal.
• day — Day. Integer, Float or Decimal.

Returned value

• A date created from the arguments.
• Type: Date.

Example

SELECT makeDate(2023, 2, 28) AS Date;

┌───────date─┐
│ 2023-02-28 │
└────────────┘

makeDateTime

Creates a DateTime from a year, month, day, hour, minute and second argument.

makeDateTime(year, month, day, hour, minute, second[, timezone])

Arguments

• year — Year. Integer, Float or Decimal.
• month — Month. Integer, Float or Decimal.
• day — Day. Integer, Float or Decimal.
• hour — Hour. Integer, Float or Decimal.
• minute — Minute. Integer, Float or Decimal.
• second — Second. Integer, Float or Decimal.
• timezone — Timezone for the returned value (optional).

Returned value

• A date with time created from the arguments.
• Type: DateTime.

Example

SELECT makeDateTime(2023, 2, 28, 17, 12, 33) AS DateTime;

┌────────────DateTime─┐
│ 2023-02-28 17:12:33 │
└─────────────────────┘

toTimeZone

Converts a date or date with time to the specified time zone. Does not change the internal value (number of unix seconds) of the data, only the value's time zone attribute and the value's string representation changes.

toTimezone(value, timezone)

Alias

toTimezone.

Arguments

• value — Time or date and time. DateTime64.
• timezone — Timezone for the returned value. String. This argument is a constant, because toTimezone changes the timezone of a column (timezone is an attribute of DateTime* types).

Returned value

• Date and time.
• Type: DateTime.

Example

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;

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

toUnixTimestamp

Converts a string, a date or a date with time to the Unix Timestamp in UInt32 representation. If the function is called with a string, it accepts an optional timezone argument.

toUnixTimestamp(date);toUnixTimestamp(str, [timezone])

Returned value

• Returns the unix timestamp.
• Type: UInt32.

Example

SELECT '2017-11-05 08:07:47' AS dt_str, toUnixTimestamp(dt_str) AS from_str, 
       toUnixTimestamp(dt_str, 'Asia/Tokyo') AS from_str_tokyo, 
       toUnixTimestamp(toDateTime(dt_str)) AS from_datetime, 
       toUnixTimestamp(toDateTime64(dt_str, 0)) AS from_datetime64 FORMAT Vertical;

Row 1:
──────
dt_str:          2017-11-05 08:07:47
from_str:        1509840467
from_str_tokyo:  1509836867
from_datetime:   1509840467
from_datetime64: 1509840467