CODEWITHSUNDEEP
phpmysqldatabase
CodeMonkey
CodeMonkey

Reputation: 2295

Converting PHP String having a unix timestamp value to a MySQL Timestamp value

I am setting a Database value within from a PHP file. In php file i have a string variable which stores unix timestamp value.

MySql table i am having is having a schema where i have to store these timestamp values in login field which is of timestamp datatype.

i tried sending 

date('Y-m-d G:i:s', strtotime($userLogin));

to my database but all it stores is 0000-00-00 00:00:00

Upvotes: 3

Views: 1017

Answers (5)

CodeMonkey
CodeMonkey

Reputation: 2295

I had to add 

    $login = date('Y-m-d H:i:s',(int)($userLogin/1000));

and it worked...

Upvotes: 0

Okan Kocyigit
Okan Kocyigit

Reputation: 13421

If $userLogin holds timestamp value than you don't need to use strtotime(),

For example, this one should work -->

$userLogin = time();
date('Y-m-d G:i:s', $userLogin);

Upvotes: 0

A_nto2
A_nto2

Reputation: 1096

MySQL provides the FROM_UNIXTIME( ) and UNIX_TIMESTAMP( ) functions to converta Unix timestamp to a MySQL date format, and vice versa.

Example:

$sql = "INSERT INTO yourtable(date, ..., ...) VALUES (FROM_UNIXTIME($yourdate), ..., ...)";

Upvotes: 2

GordonM
GordonM

Reputation: 31730

strtotime () is intended for converting strings in various date formats into UNIX timestamps. If the string is a timestamp already then it won't look like a meaningful formatted date/time to strtotime () and it will fail.

If the string is already a timestamp, then you don't need to do anything more to it than cast it to integer (strictly speaking even that step shouldn't be necessary, but casting will strip out any non-numerical characters, so it doesn't usually hurt to cast)

Additionally, MySQL is capable of parsing UNIX timestamps (with FROM_UNIXTIME(), I think, I'd have to look it up to be sure)

Upvotes: 1

lafor
lafor

Reputation: 12776

MySQL has FROM_UNIXTIME() function for this.

Upvotes: 2

Related Questions