-1
select FROM_UNIXTIME(32154654321);

Output:Null

Expected Result: Tuesday, December 9, 2988 1:55:21 PM GMT+05:30

Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62
Shikha
  • 13
  • 6

3 Answers3

2

This is the Year-2038 problem. The maximum value of the TIMESTAMP datatype is 2038-01-19 03:14:07, so you can't get the expected result.

MySQL 5.5: The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. source: https://dev.mysql.com/doc/refman/5.5/en/datetime.html

MySQL 5.6+: [...] and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'.
source: https://dev.mysql.com/doc/refman/5.7/en/datetime.html

You can find a very good answer on stackoverflow with some more details about this problem.

You can use PHP instead of MySQL to convert the timestamp (not stored in a TIMESTAMP column) to a readable datetime, with the following code:

$timestamp = 32154654321;
$format = 'Y-m-d H:i:s';
$date = new DateTime();
$date->setTimestamp($timestamp);
echo $date->format($format);

demo: http://ideone.com/DsYUOQ

Sebastian Brosch
  • 42,106
  • 15
  • 72
  • 87
2

This is why: https://dev.mysql.com/doc/refman/5.5/en/datetime.html

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

Alex Tartan
  • 6,736
  • 10
  • 34
  • 45
  • The timestamp 1499683065229 showing also null output even the year associated with this timestamp is 2017. – Shikha Jul 12 '17 at 07:19
  • that's `Mon, 23 Jan 49493 21:13:49 GMT`, according to http://www.onlineconversion.com/unix_time.htm. I think you also have milliseconds in that format. Try dividing by 1000. Current timestamp is `1499844066` – Alex Tartan Jul 12 '17 at 07:21
  • I am referring https://www.epochconverter.com/ and according to this timestamp 1499683065229 GMT: Monday, July 10, 2017 10:37:45.229 AM Your time zone: Monday, July 10, 2017 4:07:45.229 PM GMT+05:30. – Shikha Jul 12 '17 at 07:47
  • this is the output of https://www.epochconverter.com/ `Assuming that this timestamp is in milliseconds: GMT: Monday, July 10, 2017 10:37:45.229 AM`. Timestamps _do not_ have milliseconds. Since they're both numbers, why would 1499844065 == 1499683065229? – Alex Tartan Jul 12 '17 at 07:59
0

You cannot input like 32154654321

Timestamps in mysql have a maximum value of 2147483647, equivalent to 2038-01-19 05:14:07. 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 as a storage type if you require dates beyond this.

Muhammad Muazzam
  • 2,810
  • 6
  • 33
  • 62