What type should I use to save only the time (no date) in a database?

Asked

Viewed 2,036 times

5

I am in doubt which type I should use to work with hours on my system. I should use DateTime, Time or TimeStamp. I would like the hours to be saved in this format:

HH:MM
mysql sql database typing time

1 answer

11


There is one specific type for this which is the TIME. This type has the seconds and fraction of them too (keep them zeroed if you don’t need them), but in general it won’t be a problem.

But it is not always necessary to use it. There are some situations that a type VARCHAR may be enough. Of course it is more limited, does not have the best semantics, but depends on the use. If you really want to avoid seconds, it would be this way. You will have to handle the data manually.

On the other hand it is possible that this type is not sufficient for what you want and you need to use another type where you have more control, even if it is at a cost of less facilities. In some cases it may be the VARCHAR same. In others it may be interesting to use the DATETIME or TIMESTAMP, even if they have irrelevant information.

Just seeing the exact situation to decide which is best. But I’ll go on the native types first. If it is the case to sin by excess would be more advantageous in most cases.

  • I understand, in my case I’m making a schedule. Where I have the start time and the end time of an appointment. And in the future there should be a comparison so that I can not add any compromise if there is already one in the registered time. In that case it would be better to use which form?

  • I would take the TIME same. Keeping seconds always zeroed.

  • I get it, then in my java code I pass the value hh:mm for variables of the type DATE package java.sql.date ?

  • It is, then, there has to see better. In this context it can be interesting to use the DATETIME to make it compatible with Java. Even if you have superfluous information. You can use java.sql.Time. The decision is not simple. Read this: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-type-conversions.html

  • I’ll try to do it right here

  • I managed to do here, the time was saved in this format: 13:00:00.000 but I think it’s okay. Thank you.

  • I would use the Team too, if in the future you want to order it wouldn’t work with the varchar

Show 2 more comments

Browser other questions tagged mysql sql database typing time

You are not signed in. Login or sign up in order to post.