Functions for working with dates and times

**Syntax**

```
formatDateTimeInJodaSyntax(datetime, format[, timezone])
```

**Arguments**

- `datetime` — A date or date time to format. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `format` — Format string with Joda-style replacement fields. [`String`](/docs/sql-reference/data-types/string)
- `timezone` — Optional. Timezone name for the formatted time. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns time and date values according to the determined format. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Format datetime using Joda syntax**

```
SELECT formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')
```

```
┌─formatDateTimeInJodaSyntax(toDateTime('2010-01-04 12:34:56'), 'yyyy-MM-dd HH:mm:ss')─┐
│ 2010-01-04 12:34:56                                                                     │
└─────────────────────────────────────────────────────────────────────────────────────────┘
```

## fromDaysSinceYearZero[​](#fromDaysSinceYearZero "Direct link to fromDaysSinceYearZero")

Introduced in: v23.11.0

For a given number of days elapsed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero), returns the corresponding date in the [proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).

The calculation is the same as in MySQL's `FROM_DAYS()` function. The result is undefined if it cannot be represented within the bounds of the [Date](/docs/sql-reference/data-types/date) type.

**Syntax**

```
fromDaysSinceYearZero(days)
```

**Aliases**: `FROM_DAYS`

**Arguments**

- `days` — The number of days passed since year zero. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the date corresponding to the number of days passed since year zero. [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Convert days since year zero to dates**

```
SELECT
fromDaysSinceYearZero(739136) AS date1,
fromDaysSinceYearZero(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
```

```
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘
```

## fromDaysSinceYearZero32[​](#fromDaysSinceYearZero32 "Direct link to fromDaysSinceYearZero32")

Introduced in: v23.11.0

For a given number of days elapsed since [1 January 0000](https://en.wikipedia.org/wiki/Year_zero), returns the corresponding date in the [proleptic Gregorian calendar defined by ISO 8601](https://en.wikipedia.org/wiki/Gregorian_calendar#Proleptic_Gregorian_calendar).
The calculation is the same as in MySQL's `FROM_DAYS()` function. The result is undefined if it cannot be represented within the bounds of the [`Date32`](/docs/sql-reference/data-types/date32) type.

**Syntax**

```
fromDaysSinceYearZero32(days)
```

**Arguments**

- `days` — The number of days passed since year zero. [`UInt32`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns the date corresponding to the number of days passed since year zero. [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Convert days since year zero to dates**

```
SELECT
fromDaysSinceYearZero32(739136) AS date1,
fromDaysSinceYearZero32(toDaysSinceYearZero(toDate('2023-09-08'))) AS date2
```

```
┌──────date1─┬──────date2─┐
│ 2023-09-08 │ 2023-09-08 │
└────────────┴────────────┘
```

## fromModifiedJulianDay[​](#fromModifiedJulianDay "Direct link to fromModifiedJulianDay")

Introduced in: v21.1.0

Converts a [Modified Julian Day](https://en.wikipedia.org/wiki/Julian_day#Variants) number to a [Proleptic Gregorian calendar](https://en.wikipedia.org/wiki/Proleptic_Gregorian_calendar) date in text form `YYYY-MM-DD`. This function supports day number from `-678941` to `2973483` (which represent 0000-01-01 and 9999-12-31 respectively). It raises an exception if the day number is outside of the supported range.

**Syntax**

```
fromModifiedJulianDay(day)
```

**Arguments**

- `day` — Modified Julian Day number. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns date in text form. [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Convert Modified Julian Day to date**

```
SELECT fromModifiedJulianDay(58849)
```

```
┌─fromModifiedJulianDay(58849)─┐
│ 2020-01-01                   │
└──────────────────────────────┘
```

## fromModifiedJulianDayOrNull[​](#fromModifiedJulianDayOrNull "Direct link to fromModifiedJulianDayOrNull")

Introduced in: v21.1.0

Similar to [`fromModifiedJulianDay()`](#fromModifiedJulianDay), but instead of raising exceptions it returns `NULL`.

**Syntax**

```
fromModifiedJulianDayOrNull(day)
```

**Arguments**

- `day` — Modified Julian Day number. [`(U)Int*`](/docs/sql-reference/data-types/int-uint)

**Returned value**

Returns date in text form for valid `day` argument, otherwise `null`. [`Nullable(String)`](/docs/sql-reference/data-types/nullable)

**Examples**

**Convert Modified Julian Day to date with null handling**

```
SELECT fromModifiedJulianDayOrNull(58849);
SELECT fromModifiedJulianDayOrNull(60000000); -- invalid argument, returns NULL
```

```
┌─fromModified⋯Null(58849)─┐
│ 2020-01-01               │
└──────────────────────────┘
┌─fromModified⋯l(60000000)─┐
│ ᴺᵁᴸᴸ                     │
└──────────────────────────┘
```

## fromUTCTimestamp[​](#fromUTCTimestamp "Direct link to fromUTCTimestamp")

Introduced in: v22.1.0

Converts a date or date with time value from UTC timezone to a date or date with time value with the specified time zone. This function is mainly included for compatibility with Apache Spark and similar frameworks.

**Syntax**

```
fromUTCTimestamp(datetime, time_zone)
```

**Aliases**: `from_utc_timestamp`

**Arguments**

- `datetime` — A date or date with time const value or an expression. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `time_zone` — A String type const value or an expression representing the time zone. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns DateTime/DateTime64 in the specified timezone. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)

**Examples**

**Convert UTC timezone to specified timezone**

```
SELECT fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00', 3), 'Asia/Shanghai')
```

```
┌─fromUTCTimestamp(toDateTime64('2023-03-16 10:00:00',3), 'Asia/Shanghai')─┐
│                                                 2023-03-16 18:00:00.000 │
└─────────────────────────────────────────────────────────────────────────┘
```

## fromUnixTimestamp[​](#fromUnixTimestamp "Direct link to fromUnixTimestamp")

Introduced in: v20.8.0

This function converts a Unix timestamp to a calendar date and a time of a day.

It can be called in two ways:

- When given a single argument of type [`Integer`](/docs/sql-reference/data-types/int-uint), it returns a value of type [`DateTime`](/docs/sql-reference/data-types/datetime), i.e. behaves like [`toDateTime`](/docs/sql-reference/functions/type-conversion-functions#toDateTime).
- When given two or three arguments where the first argument is a value of type [`Integer`](/docs/sql-reference/data-types/int-uint), [`Date`](/docs/sql-reference/data-types/date), [`Date32`](/docs/sql-reference/data-types/date32), [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64), the second argument is a constant format string and the third argument is an optional constant time zone string, the function returns a value of type [`String`](/docs/sql-reference/data-types/string), i.e. it behaves like [`formatDateTime`](#formatDateTime).
  In this case, [MySQL's datetime format style](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-format) is used.

**Syntax**

```
fromUnixTimestamp(timestamp)
fromUnixTimestamp(timestamp[, format[, timezone]])
```