CODEWITHSUNDEEP
phpandroidmysqlstringtimestamp
Brahadeesh
Brahadeesh

Reputation: 2255

Converting string to MySQL timestamp format in php

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.

Upvotes: 19

Views: 33147

Answers (3)

PvdL
PvdL

Reputation: 1593

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

Upvotes: 36

realmfoo
realmfoo

Reputation: 433

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

Upvotes: 1

Jeff Lambert
Jeff Lambert

Reputation: 24661

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.

Upvotes: 3

Related Questions