# FROM\_UNIXTIME

## Syntax

```sql
FROM_UNIXTIME(unix_timestamp)
FROM_UNIXTIME(unix_timestamp,format)
```

## Description

Converts the number of seconds from the epoch (1970-01-01 00:00:00 UTC) to a`TIMESTAMP` value, the opposite of what [UNIX\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp) is doing. Returns NULL if the result would be outside of the valid range of `TIMESTAMP` values.

If format is given, the result is exactly equivalent to

```sql
DATE_FORMAT(FROM_UNIXTIME(unix_timestamp), format)
```

{% tabs %}
{% tab title="Current" %}
Timestamps in MariaDB have a maximum value of 4294967295, equivalent to `2106-02-07 06:28:15`. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) as a storage type if you require dates beyond this.
{% endtab %}

{% tab title="< 11.7" %}
The one-argument form of `FROM_UNIXTIME()` returns a`DATETIME`. This means that it can return values outside of valid `TIMESTAMP` range, in particular `1970-01-01 00:00:00`. And it can return the same result for different values of unix\_timestamp (around DST changes).

Timestamps in MariaDB have a maximum value of 4294967295, equivalent to `2106-02-07 06:28:15`. This is due to the underlying 32-bit limitation. Using the function on a timestamp beyond this will result in NULL being returned. Use [DATETIME](https://mariadb.com/docs/server/reference/data-types/date-and-time-data-types/datetime) as a storage type if you require dates beyond this.
{% endtab %}

{% tab title="< 11.5" %}
The one-argument form of `FROM_UNIXTIME()` returns a`DATETIME`. This means that it can return values outside of valid `TIMESTAMP` range, in particular `1970-01-01 00:00:00`. And it can return the same result for different values of unix\_timestamp (around DST changes).

The maximum value is 2147483647, equivalent to `2038-01-19 05:14:07`.
{% endtab %}
{% endtabs %}

The following options can be used by `FROM_UNIXTIME()`, as well as [DATE\_FORMAT()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_format) and [STR\_TO\_DATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date):

| Option | Description                                                                                                                                                                     |
| ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| %a     | Short weekday name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)). |
| %b     | Short form month name in current locale. For locale en\_US this is one of: Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov or Dec.                                                  |
| %c     | Month with 1 or 2 digits.                                                                                                                                                       |
| %D     | Day with English suffix 'th', 'nd', 'st' or 'rd''. (1st, 2nd, 3rd...).                                                                                                          |
| %d     | Day with 2 digits.                                                                                                                                                              |
| %e     | Day with 1 or 2 digits.                                                                                                                                                         |
| %f     | [Microseconds](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/microseconds-in-mariadb) 6 digits.                                                   |
| %H     | Hour with 2 digits between 00-23.                                                                                                                                               |
| %h     | Hour with 2 digits between 01-12.                                                                                                                                               |
| %I     | Hour with 2 digits between 01-12.                                                                                                                                               |
| %i     | Minute with 2 digits.                                                                                                                                                           |
| %j     | Day of the year (001-366)                                                                                                                                                       |
| %k     | Hour with 1 digits between 0-23.                                                                                                                                                |
| %l     | Hour with 1 digits between 1-12.                                                                                                                                                |
| %M     | Full month name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)).    |
| %m     | Month with 2 digits.                                                                                                                                                            |
| %p     | AM/PM according to current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)).    |
| %r     | Time in 12 hour format, followed by AM/PM. Short for '%I:%i:%S %p'.                                                                                                             |
| %S     | Seconds with 2 digits.                                                                                                                                                          |
| %s     | Seconds with 2 digits.                                                                                                                                                          |
| %T     | Time in 24 hour format. Short for '%H:%i:%S'.                                                                                                                                   |
| %U     | Week number (00-53), when first day of the week is Sunday.                                                                                                                      |
| %u     | Week number (00-53), when first day of the week is Monday.                                                                                                                      |
| %V     | Week number (01-53), when first day of the week is Sunday. Used with %X.                                                                                                        |
| %v     | Week number (01-53), when first day of the week is Monday. Used with %x.                                                                                                        |
| %W     | Full weekday name in current locale (Variable [lc\_time\_names](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#lc_time_names)).  |
| %w     | Day of the week. 0 = Sunday, 6 = Saturday.                                                                                                                                      |
| %X     | Year with 4 digits when first day of the week is Sunday. Used with %V.                                                                                                          |
| %x     | Year with 4 digits when first day of the week is Sunday. Used with %v.                                                                                                          |
| %Y     | Year with 4 digits.                                                                                                                                                             |
| %y     | Year with 2 digits.                                                                                                                                                             |
| %#     | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all numbers.                                               |
| %.     | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all punctation characters.                                 |
| %@     | For [str\_to\_date](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)(), skip all alpha characters.                                      |
| %%     | A literal % character.                                                                                                                                                          |

## Performance Considerations

If your [session time zone](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#time_zone) is set to `SYSTEM` (the default), `FROM_UNIXTIME()` will call the OS function to convert the data using the system time zone. At least on Linux, the corresponding function (`localtime_r`) uses a global mutex inside glibc that can cause contention under high concurrent load.

Set your time zone to a named time zone to avoid this issue. See [mysql time zone tables](https://mariadb.com/docs/server/data-types/string-data-types/character-sets/internationalization-and-localization/time-zones#mysql-time-zone-tables) for details on how to do this.

## Examples

```sql
SELECT FROM_UNIXTIME(1196440219);
+---------------------------+
| FROM_UNIXTIME(1196440219) |
+---------------------------+
| 2007-11-30 11:30:19       |
+---------------------------+

SELECT FROM_UNIXTIME(1196440219) + 0;
+-------------------------------+
| FROM_UNIXTIME(1196440219) + 0 |
+-------------------------------+
|         20071130113019.000000 |
+-------------------------------+

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
+---------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
+---------------------------------------------------------+
| 2010 27th March 01:03:47 2010                           |
+---------------------------------------------------------+
```

## See Also

* [UNIX\_TIMESTAMP()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/unix_timestamp)
* [DATE\_FORMAT()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_format)
* [STR\_TO\_DATE()](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/str_to_date)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/from_unixtime.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.