I have a query which can enter data into mysql database. However,i would like to have the time of an entry entered as well and that entered time should correspond to server time.I am using Ubuntu Server 12.04 LTS below is the code i am using to enter data into the table(it is embedded in a script)

mysql -uroot -pmysql --local-infile database -e"LOAD DATA LOCAL INFILE A.txt INTO TABLE TABLEA columns terminated by '|'"

I have created the table to have 3 columns with one for the time with datatype labled as timestamp. How can i edit my code so that it can also insert the local time. I am using a LAMP stack. To clarify further, if my table is populated like

> ip                                    mac                       time
>                        00 14 2A 2F 72 FE          2012-09-09 23:11:05

and my textfile contains the data|00 14 2A 2F 72 FF|00 21 33 RT W3 TT

and i run the script now, i would expect it to appear in the database as

> ip                                      mac                        
> time
>                           00 14 2A 2F 72 FE           2012-09-09 23:11:05
>                           00 14 2A 2F 72 FF           2013-18-09 16:11:11
>                           00 21 33 RT W3 TT           2013-18-09 16:11:11
  • If I understand you corecctly I would suggest to use the NOW() function. it takes the current DATETIME from the server. – Alvar Sep 18 '13 at 12:59
  • thank you for the suggestion, but considering that 2 columns are being inserted from the text file, and the 3rd has to be that function, how can it be implemented – sosytee Sep 18 '13 at 13:02

You can do it the same way as you do the LOAD INFILE:

mysql -uroot -pmysql 
-e "update database.TABLEA set `time`=now() where `time` IS NULL or `time` = '';"

will change TABLEA and set time for all records to now() where time IS NULL or where time is empty (since you do not suply the time stamp with the file this will only alter the new records.

Add a "where" if you need to change less records. Or something like "where time IS NULL" if the field is NULL or the actual key the records in the text file have.

2 other methods:

  1. Of course you can also provide the timestamp by adding it into the text file you upload.

  2. You can also edit the table itself and add CURRENT_TIMESTAMP as default to ONE(!) column in the table.

By the way: please do not provide the password with this method. It will be shown in your processes list thus viewable for all. More about this can be found in the official MySQL manual End-User Guidelines for Password Security

  • i have put in some edit to clarify on where i am confused – sosytee Sep 18 '13 at 13:19
  • 1
    Hey I was not done editing ;) LOL see the 3rd option; I believe that might be the easiest way for you. – Rinzwind Sep 18 '13 at 13:37
  • Thank you, i just made a script of your solution and set it as a cron job – sosytee Sep 18 '13 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.