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

Question

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

Have you confirmed the value of $userLogin is valid? It's possible that strtodate() is returning FALSE (zero). To debug, you should consider breaking the statement into two steps: 1) assign the return value of strtotime() to a variable so you can validate it, and 2) call date() using that newly created variable. — SetFreeByTruth
– SetFreeByTruth, Commented Jul 11, 2012 at 22:23

lafor · Accepted Answer · 2012-07-11 22:18:00Z

2

MySQL has FROM_UNIXTIME() function for this.

answered Jul 11, 2012 at 22:18

lafor

12.8k4 gold badges34 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

A_nto2 · Accepted Answer · 2012-07-11 22:18:47Z

2

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), ..., ...)";

answered Jul 11, 2012 at 22:18

A_nto2

1,1067 silver badges16 bronze badges

Comments

GordonM · Accepted Answer · 2012-07-11 22:18:05Z

1

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)

answered Jul 11, 2012 at 22:18

GordonM

31.9k17 gold badges94 silver badges134 bronze badges

Comments

Okan Kocyigit · Accepted Answer · 2012-07-11 22:23:41Z

0

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);

answered Jul 11, 2012 at 22:23

Okan Kocyigit

13.5k18 gold badges74 silver badges132 bronze badges

Comments

CodeMonkey · Accepted Answer · 2012-07-11 23:49:46Z

0

I had to add

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

and it worked...

answered Jul 11, 2012 at 23:49

CodeMonkey

2,3059 gold badges51 silver badges97 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related