CODEWITHSUNDEEP
mysqllinuxdatetime
Hanady
Hanady

Reputation: 789

Inserting date into mysql table using shell script

I am totally new to shell scripting, and I am trying to insert the current date to a column in a database table using bash.

Here is what I have done so far:

CREATE TABLE DiskUsage (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DiskUsage VARCHAR(50), DateOfUsage DATETIME);

The table DiskUsage is successfully created in shelltest database.

Now, I am trying to insert values in this table using shell script:

dateOfUse=$(TZ=EEST date)
echo "Date: $dateOfUse"
$(df -h > t.txt)
while read Filesystem Size Used Avail Use Mounted on
do
    mysql shelltest -e "insert into DiskUsage (DiskUsage, DateOfUsage) values ('$Use', '$dateOfUse')"
done < t.txt

But when I try to execute this script, the date value for DateOfUsage is being inserted like this:

0000-00-00 00:00:00 for all the records.

Can someone please tell me where I am mistaking? Thanks :)

Upvotes: 0

Views: 5106

Answers (2)

Zafar Malik
Zafar Malik

Reputation: 6844

Mysql default format is 'yyyy-mm-dd hh:mm:ss', so you need to insert in this mode.

Further why you are taking date from linux you can just place now() instead of $date. It will insert date automatically.

even you can leave it on mysql just set this data type as timestamp default current_timestamp and don't need to update this column as whenever row will be inserted mysql automatically insert current date time.

Upvotes: 0

Freud Chicken
Freud Chicken

Reputation: 525

Date does not spit out the format you need here per default, you need to give a format instruction

# date
Fri Apr 25 12:38:45 BST 2014

# date +'%F %T'
2014-04-25 12:38:45

so in your script it should be

dateOfUse=$(TZ=EEST date +'%F %T')

Upvotes: 3

Related Questions