I have a table in mysql which has a many columns,one of them being time.I want HOUR(time) as unique key for the table,that is there should only be one record for every hour.How can i do it?
-
So the table should have maximum 24 rows?– ypercubeᵀᴹSep 5, 2015 at 10:29
-
For a day,yes,but i have a different field for date,so it should be 24 rows for a day– hakunamatataSep 5, 2015 at 10:33
-
1I don't think this can easily be done in MySQL because you can't create an index on an expression. You need to add another column that contains the hour, create a unique index on that column and then create a trigger that updates the hour column when the time column is changed– a_horse_with_no_nameSep 5, 2015 at 11:29
-
1You can use "generated column" in MySQL 5.7 or "virtual column" on MariaDB (with unique index).– jkavalikSep 5, 2015 at 18:38
Add a comment
|
1 Answer
You could use a simple integer column as your hourly key constraint. The unique hour for each possible time can be determined by
select floor(now()/10000)
which will return something like
2015090511
for the current date 5.9.2015 at 11.25h
-
-
You could, but you would have to combine the components
year, month, day, hourinto an integer again afterextracting each one of them fromnow(). Sep 5, 2015 at 14:14