Help Inserting data in mysql table

Shell Programming and Scripting
1

Cant understand the error

#!/bin/bash

temp=""
A=""

D=$(date +"%Y-%m-%d")
H=$(date +"%R")

temp=$(wget -q -O - website | grep -o "Temperature:[[:space:]]*[0-9][0-9]*" | grep \-E -o "[0-9]+")

mysql -D "weather_wise" -e "INSERT INTO weather (Date, Hour, Degrees) VALUES ($D,$H, $temp)";

my data types for Date an Hour are both date

Im getting error below i think on $D and $H......Please help me where am i going wrong

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 ':39, 7)' at line 1

2

The type DATE evaluates to only 'YYYY-MM-DD' when you try to insert your time stamp, it will be of the form 'HH:MM'
If you were to change the "Hour" portion of your able to be of type TIME you wouldn't have any trouble inserting your 'HH:MM' into your table.

3

I'm not familiar with myslq but perhaps you must convert the date and time with makedate() and maketime() before you can inserting them.

Regards

4

i have tried that but same error though

5

I found this page with a explanation how to insert dates in mysql, hope this helps:

MySQL Tutorial - Date

Regards

6

You need to add the values in quotes

mysql -D "weather_wise" -e "INSERT INTO weather (Date, Hour, Degrees) VALUES ('$D','$H', '$temp')";
7

Franklin52 thanks for that was very helpful.....

would you know how to limit time to only hour and minute?

8

Insert the time in this format:

'0000-0-0 04:13:00'

Regards

9

will try that tho dont have quite an idea of how

10

You can do something like:

H="0000-0-0 "`date +"%R:%S"`

Regards

11

thats what i tried initially but somehow there seems to a problem with the ':'

12

What problems? There are several date versions with different behaviour, try:

H="0000-0-0 "`date +"%R":"%S"`

or:

H="0000-0-0 "`date +"%H:%M:%S"`

Regards