时间格式化失效求助：如何将13:45:00显示为1:45 PM

Hey Chris, let's work through this time formatting issue you're dealing with. It sounds like you need to convert the 24-hour time strings (like 13:45:00) from your source table into a 12-hour format with minutes and proper AM/PM labeling (like 1:45 PM) for the start_time_a field in your temporary table. Here are database-specific solutions that should get this working for you:

MySQL/MariaDB Implementation #

When inserting into your temp table, use a combination of STR_TO_DATE and DATE_FORMAT to handle the conversion:

INSERT INTO your_temp_table (start_time_a)
SELECT DATE_FORMAT(STR_TO_DATE(start_time, '%H:%i:%s'), '%l:%i %p')
FROM your_source_table;

• Quick breakdown:
  • STR_TO_DATE(start_time, '%H:%i:%s') converts your string-based time into a proper time data type (critical if your start_time is stored as varchar)
  • DATE_FORMAT(..., '%l:%i %p') formats the time to 12-hour style: %l removes leading zeros for single-digit hours, %i keeps two-digit minutes, and %p adds uppercase AM/PM with a space before it.

PostgreSQL Implementation #

Use TO_TIME and TO_CHAR to achieve the desired format:

INSERT INTO your_temp_table (start_time_a)
SELECT TO_CHAR(TO_TIME(start_time), 'FMh:MI AM')
FROM your_source_table;

• Quick breakdown:
  • TO_TIME(start_time) converts your string to a native time type
  • TO_CHAR(..., 'FMh:MI AM') uses FM to strip leading spaces from single-digit hours, h for 12-hour formatting, MI for two-digit minutes, and AM adds the period indicator with a space.

SQL Server Implementation #

You have two solid options here—pick based on performance vs. simplicity:

-- Option 1: Simpler FORMAT function (works in SQL Server 2012+)
INSERT INTO your_temp_table (start_time_a)
SELECT FORMAT(CAST(start_time AS TIME), 'h:mm tt')
FROM your_source_table;

-- Option 2: More performant CONVERT method
INSERT INTO your_temp_table (start_time_a)
SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), CAST(start_time AS TIME), 100), 7))
FROM your_source_table;

• Breakdown for the CONVERT method:
  • CONVERT(..., 100) outputs a string like 1:45PM
  • RIGHT(...,7) grabs the relevant time portion (1:45PM)
  • LTRIM removes any leading space to clean it up to 1:45 PM

Quick Notes to Avoid Headaches #

• Double-check that your start_time column is either a native time type or a consistently formatted string—if there are weird values (like missing colons or invalid hours), you’ll need to add validation first.
• Test the conversion on a small subset of data first to make sure it matches your exact desired format (tweak the format codes if you need, e.g., force two-digit hours with %I in MySQL instead of %l).

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