CODEWITHSUNDEEP
mysqldatetimetime
David542
David542

Reputation: 110562

Possibility to store a duration/time in a mysql datetime field

I have a field that for various reasons, need to accept a variety of date/time inputs. While I can use a DATETIME type to store both a date and datetime, for example:

I'm unable to figure out how to store a time. Would it be possible in any way, or would I need to store some sort of hack-value that would be used for parsing in the application, such as:

Or, is there a better way to store this?

For example, how I'm currently doing the sql is as follows:

select if(vd=date(vd), date(vd), 
       if(date(vd)='0000-01-01', right(vd,8), 
       vd)) formatted_vd, vd from spread

formatted_vd    vd
2014-01-01      2014-01-01 00:00:00
2014-01-01      01:12:23    2014-01-01 01:12:23
05:00:00        0000-01-01 05:00:00

Upvotes: 1

Views: 71

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

While MySQL does actually have a TIME type, most of the time (no pun intended), you would not want to use it. Instead, using DATETIME is usually the better choice.

That being said, if you have a DATETIME value, and for some reason you want to isolate the time component, MySQL offers a TIME() function which can extract the time by itself.

Upvotes: 2

Related Questions