{"id":29065,"date":"2024-05-06T17:14:12","date_gmt":"2024-05-06T16:14:12","guid":{"rendered":"https:\/\/nelkodev.com\/blog\/inserta-correctamente-valores-datetime-en-mysql\/"},"modified":"2024-06-03T17:44:12","modified_gmt":"2024-06-03T16:44:12","slug":"inserta-correctamente-valores-datetime-en-mysql","status":"publish","type":"post","link":"https:\/\/nelkodev.com\/en\/blog\/correctly-insert-datetime-values-in-mysql\/","title":{"rendered":"Correctly Insert Datetime Values in MySQL"},"content":{"rendered":"<p>MySQL is one of the most popular database management systems used in web application development. One of the common tasks when working with databases is the insertion of dates and times, something essential for records that depend on temporality, such as logs, transaction history, and more. In this text, I will guide you through how to insert values <code>DATETIME<\/code> in MySQL, ensuring that you can handle this important functionality effectively.<\/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\/correctly-insert-datetime-values-in-mysql\/#Entendiendo_el_Tipo_de_Dato_DATETIME\" >Understanding the DATETIME Data Type<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/nelkodev.com\/en\/blog\/correctly-insert-datetime-values-in-mysql\/#Creando_la_Tabla_para_Almacenar_Valores_datetime\" >Creating the Table to Store datetime Values<\/a><\/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\/correctly-insert-datetime-values-in-mysql\/#Insercion_Basica_de_Datos\" >Basic Data Insertion<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/nelkodev.com\/en\/blog\/correctly-insert-datetime-values-in-mysql\/#Utilizacion_de_Funciones_de_MySQL_para_la_Fecha_y_Hora\" >Using MySQL Functions for Date and Time<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/nelkodev.com\/en\/blog\/correctly-insert-datetime-values-in-mysql\/#Formatos_Flexibles_con_STR_TO_DATE\" >Flexible Formats with STR_TO_DATE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/nelkodev.com\/en\/blog\/correctly-insert-datetime-values-in-mysql\/#Manejo_de_Zonas_Horarias\" >Time Zone Management<\/a><\/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\/correctly-insert-datetime-values-in-mysql\/#Conclusion\" >Conclusion<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Entendiendo_el_Tipo_de_Dato_DATETIME\"><\/span>Understanding the DATETIME Data Type<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before we dive into data insertion, it is crucial to understand what data type is <code>DATETIME<\/code> in MySQL. <code>DATETIME<\/code> It is used to store a combination of date and time. The structure of this data type is made up of a date followed by the time: <code>YYYY-MM-DD HH:MM:SS<\/code>. This composition allows data to be handled from the year 1000 to the year 9999, with a precision of seconds.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Creando_la_Tabla_para_Almacenar_Valores_datetime\"><\/span>Creating the Table to Store datetime Values<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To get started with inserts, we first need a table that can store this type of data. Here I show you how to create a proper table in MySQL:<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">CREATE TABLE events ( id INT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(255), start_date DATETIME, end_date DATETIME );<\/code><\/pre>\n<p>In this example, <code>events<\/code> is a table that can store events with a <code>start date<\/code> y <code>end date<\/code> specified as type <code>DATETIME<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Insercion_Basica_de_Datos\"><\/span>Basic Data Insertion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Insert data into a column <code>DATETIME<\/code> it&#039;s direct. You can do this manually by specifying the date and time in the correct format (<code>YYYY-MM-DD HH:MM:SS<\/code>).<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO events (event_name, start_date, end_date) VALUES (&#039;Web Development Conference&#039;, &#039;2023-09-15 10:00:00&#039;, &#039;2023-09-15 18:00:00&#039; );<\/code><\/pre>\n<p>This SQL statement adds a new record to the table <code>events<\/code>, including specific values for the columns <code>start date<\/code> y <code>end date<\/code>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Utilizacion_de_Funciones_de_MySQL_para_la_Fecha_y_Hora\"><\/span>Using MySQL Functions for Date and Time<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>MySQL offers several features that make working with dates and times easier. For example, the function <code>NOW()<\/code> inserts the current date and time.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO events (event_name, start_date, end_date) VALUES (&#039;Planning Meeting&#039;, NOW(), DATE_ADD(NOW(), INTERVAL 2 HOUR));<\/code><\/pre>\n<p>Here, <code>NOW()<\/code> establishes the <code>start date<\/code> at the current moment, and <code>DATE_ADD()<\/code> is used to calculate the <code>end date<\/code> adding two hours to the current time.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Formatos_Flexibles_con_STR_TO_DATE\"><\/span>Flexible Formats with STR_TO_DATE<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>If you have dates in different formats, you can use the function <code>STR_TO_DATE<\/code> to convert them to the format <code>DATETIME<\/code>.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">INSERT INTO events (event_name, start_date, end_date) VALUES (&#039;Python Workshop&#039;, STR_TO_DATE(&#039;10-20-2023 15:30&#039;, &#039;%d-%m-%Y %H:1TP3 Ti&#039;), STR_TO_DATE(&#039; 10-21-2023 16:30&#039;, &#039;%d-%m-%Y %H:%i&#039;));<\/code><\/pre>\n<p>The function <code>STR_TO_DATE<\/code> Transforms a string into a specific date and time format, which makes it extremely useful when dates do not come in the standard MySQL format.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Manejo_de_Zonas_Horarias\"><\/span>Time Zone Management<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Working with time zones can be complicated. MySQL stores <code>DATETIME<\/code> no time zone information. However, you can handle this at the application level or use the type <code>TIMESTAMP<\/code> which stores UTC time automatically.<\/p>\n<pre><code class=\"&quot;language-sql&quot;\">SET time_zone = &#039;+00:00&#039;; INSERT INTO events (event_name, start_date, end_date) VALUES (&#039;JavaScript Virtual Meeting&#039;, NOW(), NOW());<\/code><\/pre>\n<p>By setting the server time zone with <code>SET time_zone<\/code>, you guarantee that all stored times correspond to the desired time zone.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Incorporate value insertion <code>DATETIME<\/code> correctly is essential for the proper control and management of dates and times in your applications. Using the methods and functions that MySQL offers, you will be able to handle these insertions effectively and tailored to your specific needs. Don&#039;t forget to always test and adapt these examples to your specific context to ensure the accuracy and efficiency of your application.<\/p>\n<p>By mastering these aspects, you will establish yourself as a more competent developer prepared to face the challenges of data management in modern applications. For more details on programming and databases, be sure to visit <a href=\"https:\/\/nelkodev.com\/en\/\">my personal blog<\/a> or contact me through <a href=\"https:\/\/nelkodev.com\/en\/contact\/\">my contact page<\/a> for any questions or advice you need.<\/p>","protected":false},"excerpt":{"rendered":"<p>MySQL is one of the most popular database management systems used in web application development. One of the common tasks when working with databases is the insertion of dates and times, which is essential for records that depend on time, such as logs, transaction history, and other data.<\/p>","protected":false},"author":1,"featured_media":29066,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[420,2185,2021],"tags":[205,472,2189,639,2125,2038,2190,352,924],"class_list":["post-29065","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","category-curso-mysql","category-modifying-data","tag-blog","tag-correctamente","tag-curso","tag-data","tag-datetime","tag-inserta","tag-modifying","tag-mysql","tag-valores"],"_links":{"self":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29065","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=29065"}],"version-history":[{"count":0,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/posts\/29065\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media\/29066"}],"wp:attachment":[{"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/media?parent=29065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/categories?post=29065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nelkodev.com\/en\/wp-json\/wp\/v2\/tags?post=29065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}