树莓派(Raspbian)下PhpMyAdmin的TIMESTAMPDIFF返回NULL问题咨询

It’s frustrating when a query works flawlessly on one database but breaks on another—especially after migrating your data over. Let’s walk through the most likely causes and fixes for your issue where TIMESTAMPDIFF returns NULL on your Raspberry Pi’s MariaDB, but runs perfectly on Windows MySQL.

First, Rule Out the Basics #

Let’s start with quick checks to eliminate simple culprits:

• Check for NULL values in settime: Run this query on your RPi MariaDB to see if any rows have a missing settime value (which would directly cause TIMESTAMPDIFF to return NULL):

  SELECT COUNT(*) FROM feedtime WHERE settime IS NULL;
  

  If this returns a number greater than 0, that’s a clear issue—but since the same data works on MySQL, it’s more likely MariaDB is treating some valid-in-MySQL values as NULL.

• Verify the settime data type: Ensure the column is a TIMESTAMP or DATETIME type on both servers. Run:

  DESCRIBE feedtime;
  

  Compare the output from both Windows MySQL and RPi MariaDB. A mismatch here (unlikely if you migrated properly) could cause unexpected behavior, but it’s worth confirming.

Most Likely Cause: Strict SQL Mode in MariaDB #

MariaDB uses stricter default SQL modes than MySQL, which can lead to invalid datetime values being converted to NULL instead of being silently accepted. Here’s how to investigate:

1. Compare SQL modes: Run this on both servers to see the difference:

   SELECT @@sql_mode;
   

   Look for modes like NO_ZERO_DATE, NO_ZERO_IN_DATE, or STRICT_TRANS_TABLES in MariaDB that aren’t present in your MySQL setup. These modes flag dates like 0000-00-00 00:00:00 or partial invalid dates as invalid, converting them to NULL.

2. Test with a valid date: To confirm the function itself works, run this on your RPi MariaDB:

   SELECT TIMESTAMPDIFF(MINUTE, CURRENT_TIMESTAMP(), '2024-12-31 23:59:59') AS timeleft;
   

   If this returns a non-NULL number, the problem is definitely with invalid settime values in your table that MariaDB is rejecting.

Fixes to Try #

• Loosen SQL mode temporarily: If you need to allow those "invalid" dates (like 0000-00-00), adjust the SQL mode for your session:

  SET sql_mode = 'ALLOW_INVALID_DATES';
  

  Re-run your original query to see if it works. If it does, you can make this change permanent by editing your MariaDB config file (usually /etc/mysql/mariadb.conf.d/50-server.cnf on RPi) and updating the sql_mode line.

• Clean up invalid settime values: If you prefer stricter compliance, update any problematic rows to use valid timestamps:

  UPDATE feedtime SET settime = '1970-01-01 00:00:01' WHERE settime = '0000-00-00 00:00:00';
  

Does the MySQL vs MariaDB Difference Matter? #

Absolutely! While MariaDB is a fork of MySQL, there are subtle but important differences in how they handle datetime validation and SQL standards compliance. MySQL is often more lenient with invalid date values by default, whereas MariaDB leans towards stricter adherence to SQL rules. This is almost certainly the root cause of your issue after migrating the database.

内容的提问来源于stack exchange，提问作者jasmeet