1

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?

4
  • So the table should have maximum 24 rows? 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 Sep 5, 2015 at 10:33
  • 1
    I 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 Sep 5, 2015 at 11:29
  • 1
    You can use "generated column" in MySQL 5.7 or "virtual column" on MariaDB (with unique index).
    – jkavalik
    Sep 5, 2015 at 18:38

1 Answer 1

1

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

2
  • You could use extract(), couldn't you?
    – dezso
    Sep 5, 2015 at 11:53
  • You could, but you would have to combine the components year, month, day, hour into an integer again after extracting each one of them from now(). Sep 5, 2015 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.