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

Updated on 28-Jan-2020 10:50:53

• Related Questions & Answers
• Why zero (0) is the first number?
• MySQL query to fetch records wherein timestamp is before 15+ days?
• Converting string to date in MongoDB?
• Converting date type SYDATUM in a format like MM/DD/YY in SAP ABAP
• MySQL query to select date from timestamp?
• Convert date string to timestamp in Python
• How to convert Python date to Unix timestamp?
• How to convert date to timestamp in MongoDB
• Converting a string to a date in JavaScript
• Convert MySQL Unix-Timestamp format to date format?
• How to select date from timestamp in MySQL?
• Convert DATE timestamp to return the month number
• Why do Indians like street food?
• What MySQL will return on adding microseconds in the timestamp value for converting it into an integer?
• Why do I lose focus when I try to work while putting on headphones?