how to add timestamp in mysql

Question

I am trying ti insert some temperature number from my sensors in my sensor table, and I want to have a time stamp when those temp. numbers are stored in my table... what time and date.

My auto time stamp in my database is -8 hours from my local time Europe/Skopje because I don't know how to change it in my database I'm trying to put my time stamp.. the type of my field is 0000-00-00 00:00:00

Here is my PHP code:

    <?php
    // Connect to MySQL

    include("dbconnect.php");
    date_default_timezone_set('Europe/Skopje');
    $Data=date("Y-m-d H:i:s");

    // Prepare the SQL statement

    $SQL = "INSERT INTO tanjaarduino.sensors (Data, sensor1 ,sensor2, sensor3, sensor4, sensor5, sensor6 ,sensor7, sensor8, sensor9, sensor10, sensor11, sensor12) VALUES (Data=".$Data.", '".$_GET["s1"]."', '".$_GET["s2"]."','".$_GET["s3"]."','".$_GET["s4"]."','".$_GET["s5"]."','".$_GET["s6"]."','".$_GET["s7"]."','".$_GET["s8"]."','".$_GET["s9"]."','".$_GET["s10"]."','".$_GET["s11"]."','".$_GET["s12"]."')";  

    echo "The time is " . $Data;
    // Execute SQL statement

    mysql_query($SQL);


?>

thanks

1) `Data=".$Data."` is a syntax-error, and `$data` is not a timestamp, its a string with a date in it. 2) *VERY* vulnerable to SQL injection, you should learn how to use MySQLi or PDO with prepared statements. 3) What data-type is your `Data` field? — Qirel, Mar 08 '17 at 16:18
You'll want to use binding rather than appending user input into an SQL statement. This script would open your database to SQL Injection attacks. As for the date insertion, you'll need to format your date to a string including the timezone. Then you should store it in a date typed field and use to_date to parse the string. — pcnate, Mar 08 '17 at 16:19
@tanjamaya read here you will understand : http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Masivuye Cokile, Mar 08 '17 at 16:19
Then after reading that stop using `mysql_*` functions they are depreciated and useless you wasting ur time with them use `mysqli` or `PDO` — Masivuye Cokile, Mar 08 '17 at 16:20

score 0 · Answer 1 · answered Mar 08 '17 at 16:42

As others have pointed out mysql_ is deprecated, so I won't dwell on that myself(but you should nonetheless address it), to answer you're actual question.

You need to wrap dates in quotes and also remove the Data=.

... VALUES (Data=".$Data.", ...

Should become

 ... VALUES ('".$Data."', ...

This is following you're current code structure, and is hould stress again that this will change if/when you update to a more suitable mysqli_/PDO system usign prepared statements, But illustrates the mistake you made which should prove useful anyway.

score -1 · Answer 2 · answered Mar 08 '17 at 17:18

<?php
// Connect to MySQL

include("dbconnect1.php");
date_default_timezone_set('Europe/Skopje');
$Data=date("Y-m-d H:i:s");

// Prepare the SQL statement

mysqli_query($dbcon,"SELECT * FROM sensors");
mysqli_query($dbcon,"INSERT INTO sensors (Data, sensor1 ,sensor2, sensor3, sensor4, sensor5, sensor6 ,sensor7, sensor8, sensor9, sensor10, sensor11, sensor12) 
VALUES ('".$Data."','".$_GET["s1"]."', '".$_GET["s2"]."','".$_GET["s3"]."','".$_GET["s4"]."','".$_GET["s5"]."','".$_GET["s6"]."','".$_GET["s7"]."','".$_GET["s8"]."','".$_GET["s9"]."','".$_GET["s10"]."','".$_GET["s11"]."','".$_GET["s12"]."')");

echo "The time is " . $Data;

mysqli_close($dbcon);

?>

how to add timestamp in mysql

2 Answers2