mysql time_format function qustion

Multi tool use
Multi tool use












1















I insert and update mysql time_format function to express string value in time,



Why do some numbers get




"truncated incorrect time value"




errors?



For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.



The type is VARCHAR (50).



my query :



INSERT INTO TABLE_HOME (DURATION) 
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))

UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')









share|improve this question

























  • As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

    – Álvaro González
    Jan 3 at 9:28
















1















I insert and update mysql time_format function to express string value in time,



Why do some numbers get




"truncated incorrect time value"




errors?



For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.



The type is VARCHAR (50).



my query :



INSERT INTO TABLE_HOME (DURATION) 
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))

UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')









share|improve this question

























  • As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

    – Álvaro González
    Jan 3 at 9:28














1












1








1








I insert and update mysql time_format function to express string value in time,



Why do some numbers get




"truncated incorrect time value"




errors?



For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.



The type is VARCHAR (50).



my query :



INSERT INTO TABLE_HOME (DURATION) 
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))

UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')









share|improve this question
















I insert and update mysql time_format function to express string value in time,



Why do some numbers get




"truncated incorrect time value"




errors?



For example, '55' is not a problem with query, but when '188' is entered, the above error message appears.



The type is VARCHAR (50).



my query :



INSERT INTO TABLE_HOME (DURATION) 
VALUES (TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s'))

UPDATE TABLE_HOME SET DURATION = TIME_FORMAT (# {DURATION, jdbcType = VARCHAR}, '% H:% i:% s')






mysql time-format






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 3 at 9:18









Vijunav Vastivch

3,3621723




3,3621723










asked Jan 3 at 9:06









MinaKimMinaKim

145




145













  • As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

    – Álvaro González
    Jan 3 at 9:28



















  • As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

    – Álvaro González
    Jan 3 at 9:28

















As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

– Álvaro González
Jan 3 at 9:28





As the name suggests TIME_FORMAT() expects a time. In that context, what do 55 or 188 mean?

– Álvaro González
Jan 3 at 9:28












2 Answers
2






active

oldest

votes


















0














TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:



mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
+------------------+-------------------+
| CAST(55 AS TIME) | CAST(188 AS TIME) |
+------------------+-------------------+
| 00:00:55 | NULL |
+------------------+-------------------+
1 row in set, 1 warning (0.00 sec)


The rules are:




MySQL recognizes TIME values in these formats:




  • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
    or 'SS'. Here D represents days and can have a value from 0 to 34.


  • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
    '10:11:12', but '109712' is illegal (it has a nonsensical minute part)
    and becomes '00:00:00'.


  • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
    alternative formats are also understood: SS, MMSS, or HHMMSS.





In this case, #3 applies:





  • 55 is rendered as SS so it's valid.


  • 188 is not a supported format so it produces NULL.


Date and time handling is already hard enough. I suggest to:




  1. Be explicit to avoid ambiguity (something like 23:30:45 is crystal clear, 188 is open to interpretations).


  2. Not use VARCHAR columns to store dates and times.







share|improve this answer

































    1














    Time_Format not all integers are valid..



    188 is not a valid time anymore



    Even where you placed it:



    "%H= Hour => 188?
    %i=Minute => 188?
    %s"=Second => 188?


    Do you think 188 is a valid time of an Hour, Minute, Second?






    share|improve this answer























      Your Answer






      StackExchange.ifUsing("editor", function () {
      StackExchange.using("externalEditor", function () {
      StackExchange.using("snippets", function () {
      StackExchange.snippets.init();
      });
      });
      }, "code-snippets");

      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "1"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019155%2fmysql-time-format-function-qustion%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      0














      TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:



      mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
      +------------------+-------------------+
      | CAST(55 AS TIME) | CAST(188 AS TIME) |
      +------------------+-------------------+
      | 00:00:55 | NULL |
      +------------------+-------------------+
      1 row in set, 1 warning (0.00 sec)


      The rules are:




      MySQL recognizes TIME values in these formats:




      • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
        or 'SS'. Here D represents days and can have a value from 0 to 34.


      • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
        '10:11:12', but '109712' is illegal (it has a nonsensical minute part)
        and becomes '00:00:00'.


      • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
        alternative formats are also understood: SS, MMSS, or HHMMSS.





      In this case, #3 applies:





      • 55 is rendered as SS so it's valid.


      • 188 is not a supported format so it produces NULL.


      Date and time handling is already hard enough. I suggest to:




      1. Be explicit to avoid ambiguity (something like 23:30:45 is crystal clear, 188 is open to interpretations).


      2. Not use VARCHAR columns to store dates and times.







      share|improve this answer






























        0














        TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:



        mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
        +------------------+-------------------+
        | CAST(55 AS TIME) | CAST(188 AS TIME) |
        +------------------+-------------------+
        | 00:00:55 | NULL |
        +------------------+-------------------+
        1 row in set, 1 warning (0.00 sec)


        The rules are:




        MySQL recognizes TIME values in these formats:




        • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
          or 'SS'. Here D represents days and can have a value from 0 to 34.


        • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
          '10:11:12', but '109712' is illegal (it has a nonsensical minute part)
          and becomes '00:00:00'.


        • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
          alternative formats are also understood: SS, MMSS, or HHMMSS.





        In this case, #3 applies:





        • 55 is rendered as SS so it's valid.


        • 188 is not a supported format so it produces NULL.


        Date and time handling is already hard enough. I suggest to:




        1. Be explicit to avoid ambiguity (something like 23:30:45 is crystal clear, 188 is open to interpretations).


        2. Not use VARCHAR columns to store dates and times.







        share|improve this answer




























          0












          0








          0







          TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:



          mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
          +------------------+-------------------+
          | CAST(55 AS TIME) | CAST(188 AS TIME) |
          +------------------+-------------------+
          | 00:00:55 | NULL |
          +------------------+-------------------+
          1 row in set, 1 warning (0.00 sec)


          The rules are:




          MySQL recognizes TIME values in these formats:




          • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
            or 'SS'. Here D represents days and can have a value from 0 to 34.


          • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
            '10:11:12', but '109712' is illegal (it has a nonsensical minute part)
            and becomes '00:00:00'.


          • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
            alternative formats are also understood: SS, MMSS, or HHMMSS.





          In this case, #3 applies:





          • 55 is rendered as SS so it's valid.


          • 188 is not a supported format so it produces NULL.


          Date and time handling is already hard enough. I suggest to:




          1. Be explicit to avoid ambiguity (something like 23:30:45 is crystal clear, 188 is open to interpretations).


          2. Not use VARCHAR columns to store dates and times.







          share|improve this answer















          TIME_FORMAT() expects a time. If you feed it with anything else, you first get a cast. In this case:



          mysql> SELECT CAST(55 AS TIME), CAST(188 AS TIME);
          +------------------+-------------------+
          | CAST(55 AS TIME) | CAST(188 AS TIME) |
          +------------------+-------------------+
          | 00:00:55 | NULL |
          +------------------+-------------------+
          1 row in set, 1 warning (0.00 sec)


          The rules are:




          MySQL recognizes TIME values in these formats:




          • As a string in 'D HH:MM:SS' format. You can also use one of the following “relaxed” syntaxes: 'HH:MM:SS', 'HH:MM', 'D HH:MM', 'D HH',
            or 'SS'. Here D represents days and can have a value from 0 to 34.


          • As a string with no delimiters in 'HHMMSS' format, provided that it makes sense as a time. For example, '101112' is understood as
            '10:11:12', but '109712' is illegal (it has a nonsensical minute part)
            and becomes '00:00:00'.


          • As a number in HHMMSS format, provided that it makes sense as a time. For example, 101112 is understood as '10:11:12'. The following
            alternative formats are also understood: SS, MMSS, or HHMMSS.





          In this case, #3 applies:





          • 55 is rendered as SS so it's valid.


          • 188 is not a supported format so it produces NULL.


          Date and time handling is already hard enough. I suggest to:




          1. Be explicit to avoid ambiguity (something like 23:30:45 is crystal clear, 188 is open to interpretations).


          2. Not use VARCHAR columns to store dates and times.








          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jan 3 at 9:51

























          answered Jan 3 at 9:35









          Álvaro GonzálezÁlvaro González

          107k30189279




          107k30189279

























              1














              Time_Format not all integers are valid..



              188 is not a valid time anymore



              Even where you placed it:



              "%H= Hour => 188?
              %i=Minute => 188?
              %s"=Second => 188?


              Do you think 188 is a valid time of an Hour, Minute, Second?






              share|improve this answer




























                1














                Time_Format not all integers are valid..



                188 is not a valid time anymore



                Even where you placed it:



                "%H= Hour => 188?
                %i=Minute => 188?
                %s"=Second => 188?


                Do you think 188 is a valid time of an Hour, Minute, Second?






                share|improve this answer


























                  1












                  1








                  1







                  Time_Format not all integers are valid..



                  188 is not a valid time anymore



                  Even where you placed it:



                  "%H= Hour => 188?
                  %i=Minute => 188?
                  %s"=Second => 188?


                  Do you think 188 is a valid time of an Hour, Minute, Second?






                  share|improve this answer













                  Time_Format not all integers are valid..



                  188 is not a valid time anymore



                  Even where you placed it:



                  "%H= Hour => 188?
                  %i=Minute => 188?
                  %s"=Second => 188?


                  Do you think 188 is a valid time of an Hour, Minute, Second?







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Jan 3 at 9:23









                  Vijunav VastivchVijunav Vastivch

                  3,3621723




                  3,3621723






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Stack Overflow!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54019155%2fmysql-time-format-function-qustion%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      7Xuwnc3tOak XuHJgD8mDpwETRxu ybVfY,pEuNGKwD2NvsGPWCz6gNeSfsvC9efLjWmUM8 VgHb qEQt4FN6dyV
                      cIMT5S CCA AqYHaH4 ucxB99El

                      Popular posts from this blog

                      Monofisismo

                      Angular Downloading a file using contenturl with Basic Authentication

                      Olmecas