如何在MySQL+PHP代码及sql_exec函数中获取时分？minute()与hour()失效问题

Hey there! Let's break down why your HOUR() and MINUTE() functions might be failing when working with MySQL, PHP, and the sql_exec function, plus how to fix it step by step.

Common Causes & Solutions #

1. Your Time Field Isn’t a Valid Date/Time Type #

First off: double-check the data type of the column you’re trying to extract hours/minutes from. If it’s stored as a plain VARCHAR (like '14:30' or '2024-05-20 14:30:00' in a text field), MySQL won’t recognize it as a proper time value—this makes HOUR()/MINUTE() return NULL or garbage results.

Fixes:

• If you can modify the table schema, convert the column to a native time type:

  ALTER TABLE your_table MODIFY COLUMN your_time_col DATETIME;
  

• If you can’t change the schema, use STR_TO_DATE() to convert the string to a valid time first:

  SELECT 
    HOUR(STR_TO_DATE(your_string_col, '%H:%i')) AS hour, 
    MINUTE(STR_TO_DATE(your_string_col, '%H:%i')) AS minute 
  FROM your_table;
  

  Adjust the format specifier (%H:%i) to match your string’s structure (e.g., '%Y-%m-%d %H:%i:%s' for full datetime strings).

2. Syntax Typos or Invalid Query Structure #

It’s easy to slip up with typos—like misspelling a column name, forgetting parentheses, or mixing up function names. Always verify your query runs correctly directly in MySQL first (via CLI, phpMyAdmin, etc.).

Example of a Correct Query:

SELECT 
  HOUR(created_at) AS hour_of_day, 
  MINUTE(created_at) AS minute_of_hour 
FROM orders 
WHERE order_id = 456;

3. sql_exec Execution & Result Handling Issues #

If your query works in MySQL but fails in PHP, the problem is likely how you’re running or fetching results with sql_exec.

Sample PHP Workflow:

// Build your query
$query = "SELECT HOUR(booking_time) AS book_hour, MINUTE(booking_time) AS book_minute FROM bookings WHERE id = ?";

// Execute with sql_exec (assuming it supports prepared statements for security)
$result = sql_exec($query, [$booking_id]);

// Fetch the result (adjust based on how sql_exec returns data)
$row = sql_fetch_assoc($result);

if ($row) {
    echo "Booking time: " . $row['book_hour'] . ":" . $row['book_minute'];
} else {
    // Add error checking to catch failed queries
    die("Query failed: " . mysql_error()); // Use mysqli_error() if using mysqli extensions
}

Make sure sql_exec isn’t hiding errors—always add checks to confirm the query ran successfully.

4. Timezone Mismatches #

If hours/minutes are off (not missing entirely), check if your MySQL server timezone differs from your PHP timezone. This can shift the time values you’re extracting.

Fix:
Set the MySQL timezone for your session before running the query:

SET time_zone = '+08:00'; -- Replace with your desired timezone (e.g., '+00:00' for UTC)

Quick Debug Hack #

Run your query directly in a MySQL client first. If it works there, the issue is in your PHP/sql_exec code. If it fails there, focus on fixing the query or data type.

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