{"id":29255,"date":"2024-05-06T04:38:06","date_gmt":"2024-05-06T03:38:06","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/dominando-el-tipo-de-dato-time-en-mysql\/"},"modified":"2024-06-03T17:45:25","modified_gmt":"2024-06-03T16:45:25","slug":"dominando-el-tipo-de-dato-time-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/","title":{"rendered":"Mastering the TIME Data Type in MySQL"},"content":{"rendered":"<p>MySQL is widely known for its efficiency and ease of use in database management. One of the most versatile and useful data types in MySQL is the type <code>TIME<\/code>. This type of data allows hours, minutes and seconds to be stored, and is essential for any developer working with applications that require detailed time management. Throughout this article, we will explore the characteristics of the data type <code>TIME<\/code> and we will learn how to use useful temporal functions to manipulate and get the best out of this type of data.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_80 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #ffffff;color:#ffffff\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #ffffff;color:#ffffff\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#Entendiendo_el_Tipo_de_Dato_TIME\" >Understanding the TIME Data Type<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#Ejemplos_Basicos_de_Uso\" >Basic Examples of Use<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#Funciones_Temporales_en_MySQL\" >Temporary Functions in MySQL<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#ADDTIME_y_SUBTIME\" >ADDTIME and SUBTIME<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#TIMEDIFF\" >TIMEDIFF<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#TIME_FORMAT\" >TIME_FORMAT<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#Aplicaciones_Practicas_del_Tipo_de_Dato_TIME\" >Practical Applications of the TIME Data Type<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/nelkodev.com\/en\/blog\/mastering-the-time-data-type-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Entendiendo_el_Tipo_de_Dato_TIME\"><\/span>Understanding the TIME Data Type<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The data type <code>TIME<\/code> in MySQL it is used to represent the duration of time or time of day, from &#039;-838:59:59&#039; until &#039;838:59:59&#039;. This means that not only can you store the time of day (as in schedules or schedules), but you can also represent time intervals that can be negative or positive if they are timing adjustments or event durations.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Ejemplos_Basicos_de_Uso\"><\/span>Basic Examples of Use<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To start, let&#039;s say you want to store the exact time a user performs an action in your app. You could create a column of type <code>TIME<\/code> in your MySQL table as follows:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE activities ( id INT AUTO_INCREMENT, user_id INT, time TIME, activity VARCHAR(255), PRIMARY KEY(id) );<\/code><\/pre>\n<p>In this case, every time a user performs an activity, you can store the exact time of day using the type <code>TIME<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Funciones_Temporales_en_MySQL\"><\/span>Temporary Functions in MySQL<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MySQL offers a variety of functions that help you work with data types <code>TIME<\/code>. These functions make it easier to manipulate, compare, and calculate temporal data. Let&#039;s look at some of the most important and useful ones:<\/p>\n<h3><span class=\"ez-toc-section\" id=\"ADDTIME_y_SUBTIME\"><\/span>ADDTIME and SUBTIME<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><code>ADDTIME()<\/code> y <code>SUBTIME()<\/code> are functions in MySQL that allow adding or subtracting a time interval from an existing hour. For example, if you want to add 3 hours and 10 minutes to a recorded time, you could do the following:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT ADDTIME(&#039;09:00:00&#039;, &#039;03:10:00&#039;);<\/code><\/pre>\n<p>The result of this query would be <code>12:10:00<\/code>.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"TIMEDIFF\"><\/span>TIMEDIFF<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>If you need to calculate the difference between two hours, <code>TIMEDIFF()<\/code> It is the function you are looking for. For example, to calculate the time difference between 9 a.m. and 5 p.m., you could use:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT TIMEDIFF(&#039;17:00:00&#039;, &#039;09:00:00&#039;);<\/code><\/pre>\n<p>This will give you as a result <code>08:00:00<\/code>, which represents eight hours of difference.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"TIME_FORMAT\"><\/span>TIME_FORMAT<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To format the output of a time, the function <code>TIME_FORMAT()<\/code> It is extremely useful. Allows you to specify the format in which you want the time to be displayed. For example, if you want the time in hours and minutes, but not seconds, you could do:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SELECT TIME_FORMAT(&#039;17:35:00&#039;, &#039;%H:%i&#039;);<\/code><\/pre>\n<p>This will return <code>17:35<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Aplicaciones_Practicas_del_Tipo_de_Dato_TIME\"><\/span>Practical Applications of the TIME Data Type<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Imagine that you are developing an application to manage employees&#039; work time. You could use the type <code>TIME<\/code> to record when an employee starts and ends their workday, and with MySQL functions, calculate hours worked, overtime, etc.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Efficient data type management <code>TIME<\/code> in MySQL can transform the way your application interacts over time. Use functions like <code>ADDTIME<\/code>, <code>SUBTIME<\/code>, <code>TIMEDIFF<\/code> y <code>TIME_FORMAT<\/code> It will allow you to perform complex calculations, comparisons and formatting that may be crucial to the business or application logic.<\/p>\n<p>I encourage you to experiment with these tools in your own projects and consider visiting <a href=\"https:\/\/nelkodev.com\/en\/\">my blog<\/a> for more resources and guides on software development, or contact me <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">here<\/a> if you have any questions or specific needs in your development projects. I&#039;d be happy to help you master MySQL and other challenges you face in your developer journey!<\/p>","protected":false},"excerpt":{"rendered":"<p>MySQL is widely known for its efficiency and ease of use in database management. One of the most versatile and useful data types in MySQL is the TIME data type. This data type allows you to store hours, minutes, and seconds, and is essential for any developer working with applications that require time.<\/p>","protected":false},"author":1,"featured_media":29256,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2033],"tags":[205,2189,639,2030,1289,352,2184,645,2194],"class_list":["post-29255","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-mysql-data-types","tag-blog","tag-curso","tag-data","tag-dato","tag-dominando","tag-mysql","tag-time","tag-tipo","tag-types"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29255","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/comments?post=29255"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29255\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29256"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29255"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29255"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29255"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}