MySQL Workbench中Timestamp格式修改求助：去除秒数保留时分

Hey there! Let's work through adjusting your timestamp from '2017-12-17 05:00:12' to '2017-12-17 05:00'. The exact approach depends on your database system—here are tailored solutions for the most common platforms:

MySQL #

For MySQL, use the DATE_FORMAT() function to either format on-the-fly for queries or permanently update the stored data:

• Query-time formatting (no permanent changes):

  SELECT DATE_FORMAT(your_timestamp_column, '%Y-%m-%d %H:%i') AS formatted_timestamp
  FROM your_table;
  

• Permanent update to stored data:

  UPDATE your_table
  SET your_timestamp_column = DATE_FORMAT(your_timestamp_column, '%Y-%m-%d %H:%i');
  

  Note: If your column uses the strict TIMESTAMP type, MySQL will automatically append :00 for seconds when storing, so the end result will match your desired format.

PostgreSQL #

PostgreSQL uses TO_CHAR() for display formatting and date_trunc() to trim timestamps to the minute:

• Query-time formatting:

  SELECT TO_CHAR(your_timestamp_column, 'YYYY-MM-DD HH24:MI') AS formatted_timestamp
  FROM your_table;
  

• Permanent truncation of stored timestamps:

  UPDATE your_table
  SET your_timestamp_column = date_trunc('minute', your_timestamp_column);
  

  This removes seconds and milliseconds from the stored value, so it will always display in the YYYY-MM-DD HH:MM format.

SQL Server #

In SQL Server, you can use CONVERT() for fast, efficient formatting or FORMAT() for more readable syntax:

• Query-time formatting:

  -- Fast option for large datasets
  SELECT CONVERT(VARCHAR(16), your_timestamp_column, 120) AS formatted_timestamp
  FROM your_table;
  
  -- More readable syntax
  SELECT FORMAT(your_timestamp_column, 'yyyy-MM-dd HH:mm') AS formatted_timestamp
  FROM your_table;
  

• Permanent update to stored data:

  UPDATE your_table
  SET your_timestamp_column = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, your_timestamp_column), 0);
  

  This calculation truncates the timestamp to the nearest minute, setting all seconds to 00.

Quick Note #

If you only need to display the timestamp without seconds (not alter the original stored data), stick with the query-time formatting methods—they’re safer and avoid risking data loss. Always back up your table before running UPDATE statements!

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