Take the 2-minute tour ×
Ask Ubuntu is a question and answer site for Ubuntu users and developers. It's 100% free, no registration required.

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
> 10.0.0.97                        00 14 2A 2F 72 FE          2012-09-09 23:11:05

and my textfile contains the data

10.0.0.97|00 14 2A 2F 72 FF
 10.0.0.98|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
> 10.0.0.97                           00 14 2A 2F 72 FE           2012-09-09 23:11:05
> 10.0.0.97                           00 14 2A 2F 72 FF           2013-18-09 16:11:11
> 10.0.0.98                           00 21 33 RT W3 TT           2013-18-09 16:11:11
share|improve this question
    
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

1 Answer 1

up vote 2 down vote accepted

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 6.1.2.1. End-User Guidelines for Password Security

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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