How to insert timestamp to postgreSQL database











up vote
0
down vote

favorite












I have a problem inserting timestamp to my psql database using Go.



I form my timestamp with this line:



datetime := currentTime.Format("02-01-2006 15:04:05")


My sql query is:



SqlStatement := `
INSERT INTO readings (date, temp, humi)
VALUES ($1, $2, $3)`


And then my call to the psql DB is:



_, err = Db.Exec(SqlStatement, datetime, temp, humi)


(As you can see I have some other variables here but they are not causing any problems.)



When I execute my code I get this error:




pq: date/time field value out of range: "21-11-2018 22:19:59"




Which as I understand it means that the format is not correct.



YET when I input exact same query directly to psql console it successfully adds record(line) to the table.



INSERT INTO readings (date, temp, humi)  VALUES ('02-01-2006 15:04:05', 20, 30);


Side note: This code worked ok before I changed column type from character(20) to timestamp, I even tried incorporating CAST in the SQL but I got the same error.










share|improve this question




















  • 1




    It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
    – mkopriva
    Nov 21 at 23:07












  • ... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
    – mkopriva
    Nov 21 at 23:14










  • @mkopriva please put in proper form not in comment.. :)
    – dwir182
    Nov 22 at 1:21










  • @mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
    – Robert
    Nov 22 at 12:13















up vote
0
down vote

favorite












I have a problem inserting timestamp to my psql database using Go.



I form my timestamp with this line:



datetime := currentTime.Format("02-01-2006 15:04:05")


My sql query is:



SqlStatement := `
INSERT INTO readings (date, temp, humi)
VALUES ($1, $2, $3)`


And then my call to the psql DB is:



_, err = Db.Exec(SqlStatement, datetime, temp, humi)


(As you can see I have some other variables here but they are not causing any problems.)



When I execute my code I get this error:




pq: date/time field value out of range: "21-11-2018 22:19:59"




Which as I understand it means that the format is not correct.



YET when I input exact same query directly to psql console it successfully adds record(line) to the table.



INSERT INTO readings (date, temp, humi)  VALUES ('02-01-2006 15:04:05', 20, 30);


Side note: This code worked ok before I changed column type from character(20) to timestamp, I even tried incorporating CAST in the SQL but I got the same error.










share|improve this question




















  • 1




    It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
    – mkopriva
    Nov 21 at 23:07












  • ... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
    – mkopriva
    Nov 21 at 23:14










  • @mkopriva please put in proper form not in comment.. :)
    – dwir182
    Nov 22 at 1:21










  • @mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
    – Robert
    Nov 22 at 12:13













up vote
0
down vote

favorite









up vote
0
down vote

favorite











I have a problem inserting timestamp to my psql database using Go.



I form my timestamp with this line:



datetime := currentTime.Format("02-01-2006 15:04:05")


My sql query is:



SqlStatement := `
INSERT INTO readings (date, temp, humi)
VALUES ($1, $2, $3)`


And then my call to the psql DB is:



_, err = Db.Exec(SqlStatement, datetime, temp, humi)


(As you can see I have some other variables here but they are not causing any problems.)



When I execute my code I get this error:




pq: date/time field value out of range: "21-11-2018 22:19:59"




Which as I understand it means that the format is not correct.



YET when I input exact same query directly to psql console it successfully adds record(line) to the table.



INSERT INTO readings (date, temp, humi)  VALUES ('02-01-2006 15:04:05', 20, 30);


Side note: This code worked ok before I changed column type from character(20) to timestamp, I even tried incorporating CAST in the SQL but I got the same error.










share|improve this question















I have a problem inserting timestamp to my psql database using Go.



I form my timestamp with this line:



datetime := currentTime.Format("02-01-2006 15:04:05")


My sql query is:



SqlStatement := `
INSERT INTO readings (date, temp, humi)
VALUES ($1, $2, $3)`


And then my call to the psql DB is:



_, err = Db.Exec(SqlStatement, datetime, temp, humi)


(As you can see I have some other variables here but they are not causing any problems.)



When I execute my code I get this error:




pq: date/time field value out of range: "21-11-2018 22:19:59"




Which as I understand it means that the format is not correct.



YET when I input exact same query directly to psql console it successfully adds record(line) to the table.



INSERT INTO readings (date, temp, humi)  VALUES ('02-01-2006 15:04:05', 20, 30);


Side note: This code worked ok before I changed column type from character(20) to timestamp, I even tried incorporating CAST in the SQL but I got the same error.







sql postgresql go timestamp






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 at 7:48









Flimzy

36.6k96496




36.6k96496










asked Nov 21 at 22:30









Robert

104




104








  • 1




    It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
    – mkopriva
    Nov 21 at 23:07












  • ... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
    – mkopriva
    Nov 21 at 23:14










  • @mkopriva please put in proper form not in comment.. :)
    – dwir182
    Nov 22 at 1:21










  • @mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
    – Robert
    Nov 22 at 12:13














  • 1




    It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
    – mkopriva
    Nov 21 at 23:07












  • ... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
    – mkopriva
    Nov 21 at 23:14










  • @mkopriva please put in proper form not in comment.. :)
    – dwir182
    Nov 22 at 1:21










  • @mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
    – Robert
    Nov 22 at 12:13








1




1




It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
– mkopriva
Nov 21 at 23:07






It's the 21 that's out of range because postgres timestamps are by default MM-DD-YYYY ... . So just switch 21 with 11 and you're good to go.
– mkopriva
Nov 21 at 23:07














... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
– mkopriva
Nov 21 at 23:14




... alternatively you can change the datestyle setting with SET datestyle TO dmy; (postgresql.org/docs/9.4/…)
– mkopriva
Nov 21 at 23:14












@mkopriva please put in proper form not in comment.. :)
– dwir182
Nov 22 at 1:21




@mkopriva please put in proper form not in comment.. :)
– dwir182
Nov 22 at 1:21












@mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
– Robert
Nov 22 at 12:13




@mkopriva I did that already before so I tought it was not a problem, BUT it turns out that I have to execute that sql before each write to the db. Wierd but it works now.
– Robert
Nov 22 at 12:13












2 Answers
2






active

oldest

votes

















up vote
0
down vote













I can see that datetime variable is string type. I don't know why you must convert time.Time to string before exec query.

If define date column in readings table is TIMESTAMP, you can exec query like this
Db.Exec(SqlStatement, currentTime, temp, humi)






share|improve this answer




























    up vote
    0
    down vote













    Solved it by adding



    SET datestyle TO dmy;


    Before each



    _, err = Db.Exec(SqlStatement, datetime, temp, humi)


    So now I have two Db.Exec functions in a row, one is sending "SET datestyle TO dmy" and the other is this one that is sending actual data.



    Wierd.






    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',
      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%2f53421338%2fhow-to-insert-timestamp-to-postgresql-database%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








      up vote
      0
      down vote













      I can see that datetime variable is string type. I don't know why you must convert time.Time to string before exec query.

      If define date column in readings table is TIMESTAMP, you can exec query like this
      Db.Exec(SqlStatement, currentTime, temp, humi)






      share|improve this answer

























        up vote
        0
        down vote













        I can see that datetime variable is string type. I don't know why you must convert time.Time to string before exec query.

        If define date column in readings table is TIMESTAMP, you can exec query like this
        Db.Exec(SqlStatement, currentTime, temp, humi)






        share|improve this answer























          up vote
          0
          down vote










          up vote
          0
          down vote









          I can see that datetime variable is string type. I don't know why you must convert time.Time to string before exec query.

          If define date column in readings table is TIMESTAMP, you can exec query like this
          Db.Exec(SqlStatement, currentTime, temp, humi)






          share|improve this answer












          I can see that datetime variable is string type. I don't know why you must convert time.Time to string before exec query.

          If define date column in readings table is TIMESTAMP, you can exec query like this
          Db.Exec(SqlStatement, currentTime, temp, humi)







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 22 at 3:12









          KibGzr

          1,103510




          1,103510
























              up vote
              0
              down vote













              Solved it by adding



              SET datestyle TO dmy;


              Before each



              _, err = Db.Exec(SqlStatement, datetime, temp, humi)


              So now I have two Db.Exec functions in a row, one is sending "SET datestyle TO dmy" and the other is this one that is sending actual data.



              Wierd.






              share|improve this answer

























                up vote
                0
                down vote













                Solved it by adding



                SET datestyle TO dmy;


                Before each



                _, err = Db.Exec(SqlStatement, datetime, temp, humi)


                So now I have two Db.Exec functions in a row, one is sending "SET datestyle TO dmy" and the other is this one that is sending actual data.



                Wierd.






                share|improve this answer























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  Solved it by adding



                  SET datestyle TO dmy;


                  Before each



                  _, err = Db.Exec(SqlStatement, datetime, temp, humi)


                  So now I have two Db.Exec functions in a row, one is sending "SET datestyle TO dmy" and the other is this one that is sending actual data.



                  Wierd.






                  share|improve this answer












                  Solved it by adding



                  SET datestyle TO dmy;


                  Before each



                  _, err = Db.Exec(SqlStatement, datetime, temp, humi)


                  So now I have two Db.Exec functions in a row, one is sending "SET datestyle TO dmy" and the other is this one that is sending actual data.



                  Wierd.







                  share|improve this answer












                  share|improve this answer



                  share|improve this answer










                  answered Nov 22 at 12:16









                  Robert

                  104




                  104






























                      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.





                      Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                      Please pay close attention to the following guidance:


                      • 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%2f53421338%2fhow-to-insert-timestamp-to-postgresql-database%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







                      Popular posts from this blog

                      A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                      Calculate evaluation metrics using cross_val_predict sklearn

                      Insert data from modal to MySQL (multiple modal on website)