Functions for working with dates and times

**Syntax**

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

**Aliases**: `FROM_UNIXTIME`

**Arguments**

- `timestamp` — Unix timestamp or date/date with time value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `format` — Optional. Constant format string for output formatting. [`String`](/docs/sql-reference/data-types/string)
- `timezone` — Optional. Constant time zone string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns `DateTime` of the timestamp when called with one argument, or a String when called with two or three arguments. [`DateTime`](/docs/sql-reference/data-types/datetime) or [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Convert Unix timestamp to DateTime**

```
SELECT fromUnixTimestamp(423543535)
```

```
┌─fromUnixTimestamp(423543535)─┐
│          1983-06-04 10:58:55 │
└──────────────────────────────┘
```

**Convert Unix timestamp with format**

```
SELECT fromUnixTimestamp(1234334543, '%Y-%m-%d %R:%S') AS DateTime
```

```
┌─DateTime────────────┐
│ 2009-02-11 14:42:23 │
└─────────────────────┘
```

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

Introduced in: v23.1.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 [`formatDateTimeInJodaSyntax`](#formatDateTimeInJodaSyntax). In this case, [Joda datetime format style](https://joda-time.sourceforge.net/apidocs/org/joda/time/format/DateTimeFormat.html) is used.

**Syntax**

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

**Arguments**

- `timestamp` — Unix timestamp or date/time value. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Date`](/docs/sql-reference/data-types/date) or [`Date32`](/docs/sql-reference/data-types/date32) or [`DateTime`](/docs/sql-reference/data-types/datetime) or [`DateTime64`](/docs/sql-reference/data-types/datetime64)
- `format` — Optional. Constant format string using Joda syntax for output formatting. [`String`](/docs/sql-reference/data-types/string)
- `timezone` — Optional. Constant time zone string. [`String`](/docs/sql-reference/data-types/string)

**Returned value**

Returns a date with time when called with one argument, or a String when called with two or three arguments.} [`DateTime`](/docs/sql-reference/data-types/datetime) or [`String`](/docs/sql-reference/data-types/string)

**Examples**

**Convert Unix timestamp with Joda format**

```
SELECT fromUnixTimestampInJodaSyntax(1234334543, 'yyyy-MM-dd HH:mm:ss', 'UTC') AS DateTime
```

```
┌─DateTime────────────┐
│ 2009-02-11 06:42:23 │
└─────────────────────┘
```

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

Introduced in: v22.6.0

Creates a `Date` from either:

- a year, month and day
- a year and day of year

**Syntax**

```
makeDate(year, month, day)
makeDate(year, day_of_year)
```

**Arguments**

- `year` — Year number. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `month` — Month number (1-12). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `day` — Day of the month (1-31). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `day_of_year` — Day of the year (1-365). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a `Date` value constructed from the provided arguments [`Date`](/docs/sql-reference/data-types/date)

**Examples**

**Date from a year, month, day**

```
SELECT makeDate(2023, 2, 28) AS date;
```

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

**Date from year and day of year**

```
SELECT makeDate(2023, 42) AS date;
```

```
┌───────date─┐
│ 2023-02-11 │
└────────────┘
```

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

Introduced in: v22.6.0

Creates a `Date32` from either:

- a year, month and day
- a year and day of year

**Syntax**

```
makeDate32(year, month, day)
makeDate32(year, day_of_year)
```

**Arguments**

- `year` — Year number. [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `month` — Month number (1-12). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `day` — Day of the month (1-31). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)
- `day_of_year` — Day of the year (1-365). [`(U)Int*`](/docs/sql-reference/data-types/int-uint) or [`Float*`](/docs/sql-reference/data-types/float) or [`Decimal`](/docs/sql-reference/data-types/decimal)

**Returned value**

Returns a `Date32` value constructed from the provided arguments [`Date32`](/docs/sql-reference/data-types/date32)

**Examples**

**Date32 from a year, month, day**

```
SELECT makeDate(2023, 2, 28) AS date;
```

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

**Date32 from year and day of year**

```
SELECT makeDate(2023, 42) AS date;
```

```
┌───────date─┐
│ 2023-02-11 │
└────────────┘
```

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

Introduced in: v22.6.0

Creates a `DateTime` from year, month, day, hour, minute, and second, with optional timezone.

**Syntax**

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

**Arguments**