Inserting date into mysql table using shell script

Question

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 :)

The output is working fine: Date: Sun Mar 10 13:39:25 EEST 2013 — Hanady
– Hanady, Commented Apr 25, 2014 at 11:39
why you not use NOW() in query instead of linux date format? — Ilesh Patel
– Ilesh Patel, Commented Apr 25, 2014 at 11:43
mmm actually as I mentioned before, i am new to shell scripting that's why i didn't think about using this function. — Hanady
– Hanady, Commented Apr 25, 2014 at 11:47
@Hanady: NOW() actually is a current_timestamp. If you want to use specific date then you follow answer, by @FreudChicken, below. — Ravinder Reddy
– Ravinder Reddy, Commented Apr 25, 2014 at 11:49

Freud Chicken · Accepted Answer · 2014-04-25 11:45:29Z

3

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')

answered Apr 25, 2014 at 11:45

Freud Chicken

5253 silver badges8 bronze badges

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

Comments

Zafar Malik · Accepted Answer · 2014-04-25 11:47:37Z

0

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.

answered Apr 25, 2014 at 11:47

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Collectives™ on Stack Overflow

Inserting date into mysql table using shell script

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related