Insert timestamp into mysql column in shell

Question

The following works for me:

mysql -u 'root' -h 8.8.8.88 mo -e 'UPDATE `path_last_updated` 
    SET timestamp="2012-01-03 00:00:00"'

However, the following does not:

TIMESTAMP=`date "+%Y-%m-%d-%T"`
mysql -u 'root' -h 8.8.8.88 mo -e 'UPDATE `path_last_updated` 
    SET timestamp=$TIMESTAMP'

How would I insert the timestamp from unix into my mysql table?

Update:

TIMESTAMP=`date "+%Y-%m-%d %T"`
mysql -u 'root' -h 8.8.8.88 mo -e "UPDATE `path_last_updated` 
    SET timestamp='$TIMESTAMP'"

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual 
that corresponds to your MySQL server version for the right syntax to use near 'SET 
timestamp='2013-01-31 15:46:00'' at line 1

Community · Accepted Answer · 2017-05-23 10:31:59Z

2

Shell variable interpolation only works between double quotes ("), not single ('). You've also got backticks in there, which in a double-quoted string will be treated as an embedded shell command.

Try:

mysql -u 'root' -h 8.8.8.88 mo -e "UPDATE \`path_last_updated\`
    SET timestamp='$TIMESTAMP'"

Also, fwiw, you have an extra dash (-) in your format for the date command, between the %d and %T.

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Jan 31, 2013 at 23:41

martin clayton

78.6k33 gold badges220 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

David542 Over a year ago

Ok, thanks. The first half is now working for me -- it's executing with the variable. But it doesn't seem to be a valid SQL statement for whatever reason. Please see updated question.

martin clayton Over a year ago

Seems like you've got it sorted anyway. I've added a comment on the backticks.

David542 · Accepted Answer · 2013-01-31 23:57:43Z

0

ALTER TABLE to make it easier:

ALTER TABLE path_last_updated ADD date_entered timestamp DEFAULT CURRENT_TIMESTAMP

And then in shell:

mysql -u 'root' -h 8.8.8.88 mo -e "UPDATE path_last_updated SET timestamp=DEFAULT"

answered Jan 31, 2013 at 23:57

David542

112k211 gold badges581 silver badges1.1k bronze badges

Collectives™ on Stack Overflow

Insert timestamp into mysql column in shell

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related