timestamp to datetime

I must have asked this question about 15 times lol

How do I convert a PHP timestamp 1128831859 into MySQL datetime format 0000-00-00 00:00:00

Is it something like

$datetime = date(Y-m-d h:j:s, $timestamp);


??

 

date("Y-m-d H:i:s", $timestamp);

Code (markup):

 

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

 

You can do it with MySQL and the appropriate date function, FROM_UNIXTIME, that'll spare you the formatting.

 

Thanks but I'm putting it into the format to enter the date into the DB. The above solutions were what I had been looking for.

I appreciate the help though

 

date("Y-M-d-D H:i", $timestamp);
This is what im using this will output something like this 2013-Jan-08-Tue 11:40

 

Why would you save the formatted timestamp into MySQL !? Just save it as UNIX integer and make a function to parse it whenever you need it. Let's suppose you may want to work with those dates, you'd be forced to work with strtotime which is not that convenient.

 

Because the DATATIME data type in SQL allows or a wider date range with no 2039 it's gonna blow up, and is generally easier/faster to deal with when building queries -- particularly if you are going to use SQL functions like DATE_ADD with things like INTERVAL and metrics like DAY or YEAR.

Letting you keep PHP as the glue and having mySQL do the heavy lifting, instead of brute-forcing things the hard way by dicking around with unix time.

Which is probably why if possible I'd use now() or DATE_ADD( NOW(), expression ) instead of trying to copy a *nix timestamp over from PHP.

 

I think you're underestimating the use of "DATATIME" (which is actually "DATETIME") and the INT(10) field type in MySQL. I prefer using INT(10) and inserting a unix_timestamp() into the MySQL since i'm not interested in indexing it, this way you'll get more than 2039 .

PS. Lean to be less stubborn and arogant because you couldn't know how was I going to use the field

 

Typo -- common when on these POS laptop keyboards instead of the Model M at my workstation.

While I would prefer not to use something as limited as a unix timestamp given that at 32 bits it's blowing up in few decades (though if you're on 64 bit that's a non-issue)... But my big point though is why send a timestamp from your PHP when you could just have mySQL use it's own internally? One that you can perform relational queries on with ease? That way you don't have to play with your dates at all from the PHP -- let the query do the work. In my own code I've stopped passing PHP dates to anything that is just pulling the current time and/or is relational to it... it just makes sense when mySQL has a data format for dates, the ability to internally perform math on dates, and the ability to do actions like:

	'addUserSession' => '
		INSERT INTO !prefix!sessions (
			sessionId,userId,expires,sessionTime,ipAddress,userAgent
		) VALUES (
			:sessionId,
			:userId,
			DATE_ADD(NOW(), INTERVAL :expires SECOND),
			:expires,
			:ipAddress,
			:userAgent
		)
	',
	
	'updateUserSessionExpire' => '
		UPDATE !prefix!sessions
		SET expires = DATE_ADD(NOW(), INTERVAL sessionTime SECOND)
		WHERE sessionId = :sessionId;
	',

	'purgeExpiredSessions' => '
		DELETE FROM !prefix!sessions
		WHERE expires < NOW();
	',

Code (markup):

Funny, I thought this was about how the OP was going to use it --not you! ...and your asking why they'd want to use it that way. But of course answering your question is somehow being "arrogant" and "stubborn" -- RIGHT.

Really though, we don't know how Weirfire was going to use it or what the date source is -- again why without code, an explanation of what it's being used for, etc, etc -- we're all guessing wildly in the dark.

But we can make that an educated guess since they did ask about DATETIME... so one would assume they were using it for the reasons I outlined.

 

I guess this is the time when I should argue with you and create a smartass-wiseman loop (especially those you're trying to get me into ?)