2

I was trying to make a column that stores the showtime of movies.

Since it is more meaningful to have showtimes in the unit of minutes, can I have a date type that has only years to minutes (eg. 2017-12-26 15:30), but no seconds? Thanks!

4

There is no special datatype for this :) Instead, you can use DATE_FORMAT

Example:

SELECT DATE_FORMAT(now(), '%Y-%m-%d %H:%i');

So you can create a view to converting your datetime column to the specific date format.

Create table with Datetime

create table test (date datetime);
insert into test values (now());
select * from test;
2017-12-28 10:30:24

Create view to convert date format

create view v_test as (select DATE_FORMAT(date, '%Y-%m-%d %H:%i'));

select * from v_test;
2017-12-28 10:30
| improve this answer | |
  • So is it okay if I just do DATA_FORMAT() and ignore the %s completely? – jackxujh Dec 28 '17 at 10:52
  • Yeah, its enough. – Bhuvanesh Dec 28 '17 at 10:58
  • Okay. One more thing... What about storing HH and MM as deprecate integers (to save the unnecessary space for seconds)? – jackxujh Dec 28 '17 at 11:03
  • See, your data in already stored in the table, there is no straight forward method to store the data as you like. So we created views, no need for additional storage for this. – Bhuvanesh Dec 28 '17 at 11:20
  • I found this method cannot be used in a generated column in the same table without modification. What tweaks should be made to have this in a generated column in the same table? Thanks! – jackxujh Dec 28 '17 at 15:26

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.