如何在含INNER JOIN的数据库关联查询中获取AM/PM格式时间

Hey there! Let's tackle your two questions about retrieving and formatting times in AM/PM format from databases—starting with the general approach, then diving into your specific code scenario.

Different databases have their own built-in functions to convert datetime values to AM/PM format. Here are the most common implementations:

• MySQL/MariaDB: Use TIME_FORMAT() for time-only columns, or DATE_FORMAT() for datetime columns. The %r specifier returns the 12-hour format with AM/PM:

  -- For a TIME column
  SELECT TIME_FORMAT(your_time_column, '%r') AS formatted_time FROM your_table;
  -- For a DATETIME column
  SELECT DATE_FORMAT(your_datetime_column, '%r') AS formatted_time FROM your_table;
  

• PostgreSQL: Use the TO_CHAR() function with the 'HH12:MI:SS AM' format string:

  SELECT TO_CHAR(your_datetime_column, 'HH12:MI:SS AM') AS formatted_time FROM your_table;
  

• SQL Server: Use FORMAT() with the 'hh:mm:ss tt' format:

  SELECT FORMAT(your_datetime_column, 'hh:mm:ss tt') AS formatted_time FROM your_table;
  

• Oracle: Use TO_CHAR() with the 'HH:MI:SS AM' format mask:

  SELECT TO_CHAR(your_datetime_column, 'HH:MI:SS AM') AS formatted_time FROM your_table;
  

Looking at your code snippet, there are a few small adjustments needed to get the AM/PM formatting working correctly and ensure your query runs smoothly:

1. Fix column qualification and alias #

You’re referencing LOG_IN without the database/table prefix in the TIME_FORMAT call (even though you used it earlier for the raw LOG_IN column). Adding an alias to the formatted column also makes it easier to reference in your results later.

2. Correct quote usage and table syntax #

In most MySQL configurations, double quotes are treated as identifier quotes instead of string literals—use single quotes for the %r format specifier. Also, wrapping database/table names in backticks avoids errors if your names have spaces or reserved words.

3. Don’t forget the JOIN condition #

Your query was truncated, but you’ll need an ON clause to define how student_login and student_data are related (like matching student IDs) for the INNER JOIN to work.

Corrected Code Snippet #

Here’s the adjusted version with explanations:

$event_name = 'database_name'; // Remove spaces here if your actual DB name doesn't have them
$conn = mysqli_connect($servername, $username, $password);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

// Use backticks to escape database/table names safely
$sql = "
    SELECT 
        `$event_name`.`student_login`.`LOG_IN` AS original_login_time,
        TIME_FORMAT(`$event_name`.`student_login`.`LOG_IN`, '%r') AS formatted_login_time,
        `event_attendance`.`student_data`.* 
    FROM 
        `$event_name`.`student_login`
    INNER JOIN 
        `event_attendance`.`student_data` 
        ON `$event_name`.`student_login`.`student_id` = `event_attendance`.`student_data`.`student_id` -- Add your actual join condition here
    -- Add WHERE clauses if needed, e.g., WHERE some_column = 'value'
";

// Execute and process the query
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_assoc($result)) {
        echo "Original Login Time: " . $row["original_login_time"] . "<br>";
        echo "Formatted Time (AM/PM): " . $row["formatted_login_time"] . "<br>";
        // Access other student_data columns as needed
    }
} else {
    echo "No matching records found";
}

mysqli_close($conn);

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