Modify TIMESTAMP MySQL for use as Primary Key

Question

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).

If you round them, you are much more likely to end up with duplicates. Is that a problem, since this is a primary key? — Andrew Barber, Dec 30 '14 at 20:09

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

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

rounding time to the nearest 15 minutes in php/mysql

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;

edited May 23 '17 at 10:26

Community

1
1

answered Dec 31 '14 at 13:24

Jerry Sweeton

163
2
4
15

Modify TIMESTAMP MySQL for use as Primary Key

1 Answers1