Converting string to MySQL timestamp format in php

Question

I am writing a query in php using a string sent from a android java application.
The query is something like :

$insertSQL = sprintf("INSERT INTO app_DuckTag (taste) VALUES (%s) WHERE species=%s AND timestamp=%s",
                         GetSQLValueString($_POST['taste'], "text"),
                         GetSQLValueString($_POST['species'], "text"),
                         GetSQLValueString($_POST['timestamp'], "text"));

But I doubt timestamp is stored as a string inside MySQL.
How should I convert the string to time format as in MySQL?
The strtotime(string) php function converts it to unix time.
But the MySQL stores it differently, I guess. Thank you.

EDIT: This is how it shows up in MySQL phpmyadmin: 2011-08-16 17:10:45

EDIT: My query is wrong though. Cannon use a where clause with Insert into.
The query has to be UPDATE .... SET ... = ... WHERE ....
But the accepted answer is the correct way to use the time inside the WHERE clause.

Solution 1

This should be all:

date("Y-m-d H:i:s", strtotime($_POST['timestamp']));

if you want to check for timezones and such you should use strftime instead of date

Solution 2

If you can get the input 'string' (which you haven't provided the format that you're receiving it in) to a Unix timestamp value, you can easily convert it to a MySQL datetime format like this:

$mysqldatetime = date("Y-m-d H:i:s", $unixTimeStampValue);

This will produce a string similar to the following:

2011-08-18 16:31:32

Which is the format of the MySQL datetime format. If you are using a different time format, then the first argument to the date function will be different.

See the manual for the date function for more information and other ways you can format the value that it returns.

Edit

You are receiving a string formatted in MySQL datetime format. After sanitizing it, you can insert it directly into the database. To MySQL it may be a 'datetime' data type, but to PHP it is simply a string (just like your entire SQL query is nothing more than a string to PHP).

$timestamp = mysql_real_escape_string($_POST['timestamp']);

You should be able to safely insert that into your database for the timestamp field.

Solution 3

There is a function FROM_UNIXTIME in mysql. Why don't you want to use it?

Comments

[Brahadeesh]

I receive it in exact same way but as a string - "2011-08-16 17:10:45"

[Brahadeesh]

Will check and let you know. I guess if I can insert it into a timestamp field then I must be able to check if its the same in a WHERE clause too.

[Brahadeesh]

Because I do not have the time in UNIX format. I have it exactly like I want but as a string. I have accepted an answer that works.