Search code examples
mysqltimestampmariadbheidisql

Modify TIMESTAMP MySQL for use as Primary Key

I have several tables in a MySQL database where I have defined my TIME_GMT (TIMESTAMP) as the primary key. Problem is my TIME_GMT is never exactly the same between the tables.

For example, my first table (first row) has a primary key of 2014-12-30 05:00:05. My next table (first row) has an primary key of 2014-12-14 05:00:07, and so on.

How would I go about modifying these primary keys to 2014-12-30 05:00:00 (round down to the lowest 10 seconds).

Solution

  • After a bit more searching, I found this question that was previously asked and answered.

    I was able take the solution and modify my code.

    SELECT
    SEC_TO_TIME((TIME_TO_SEC(TIME_GMT) DIV 10) * 10) AS TIME_GMT_MODIFY 
FROM table;