Why I got output 0(Zero) on converting date like ‘1965-05-15’ to TIMESTAMP?

As we know that with the help of MySQL UNIX_TIMESTAMP function, we can produce the number of seconds from given date/DateTime. But when we try to convert a date like ‘1965-05-15’ it would give 0(Zero) as output because the range of TIMESTAMP is between ‘1970-01-01 00:00:01’ to ‘2038-01-19 08:44:07’. Hence, the date values beyond TIMESTAMP range cannot be converted and will return 0 as output always.

Examples are given below −

mysql> Select UNIX_TIMESTAMP ('1965-05-15');
+----------------------------------------------+
| unix_timestamp('1965-05-15 05:04:30')        |
+----------------------------------------------+
|                                       0      |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select UNIX_TIMESTAMP ('1970-05-15 05:04:30');
+----------------------------------------------+
| unix_timestamp('1970-05-15 05:04:30')        |
+----------------------------------------------+
|                                     11576070 |
+----------------------------------------------+
1 row in set (0.00 sec)

Mkotla

Published on 19-Feb-2018 07:00:35

• Related Questions & Answers
• Why in MySQL, we cannot use arithmetic operators like ‘=’, ‘<’ or ‘<>’ with NULL?
• Validate input: replace all ‘a’ with ‘@’ and ‘i’ with ‘!’JavaScript
• ASCII NUL, ASCII 0 (‘0’) and Numeric literal 0?
• Why zero (0) is the first number?
• Rearrange an array such that ‘arr[j]’ becomes ‘i’ if ‘arr[i]’ is ‘j’ in C++
• On inserting ‘NULL’, ‘0’ or No Value to the column, will MySQL assign sequence number for AUTO_INCREMENT column?
• How to Create a RAID 0 Storage Array with ‘mdadm’ on Ubuntu 16.04
• Why are South Africans called ‘Protease’?
• What MySQL returns on using any other character than ‘T’ or ‘Space’ between date and time parts?
• Learn Why ‘less’ is Faster Than ‘more’ Command for Effective File Navigation
• How can I store ‘0000-00-00’ as a date in MySQL?
• I used ‘from’ and ‘to’ with backticks as column titles in my database table. Now, how do I SELECT them?
• Python program to replace first ‘K’ elements by ‘N’
• Are ‘this’ and ‘super’ keywords in Java?
• ‘AND’ vs ‘&&’ operators in PHP