Formatting Time When Inserting Into MySQL Table

Sharif

How can I format the time that is inserted into a mysql field? Here is the code I use (this is on a page that uses a form to insert into the database:

INSERT INTO ... , date=now()";

I have to use the now() function to insert the current date and time into the field and it works very well. My problem is this: my webhost's sql server is in Kansas, which is on the central time zone. Every thing on my website should show up by eastern time so here is where the problem occurs: If I post a article through the form at 1:00PM, it shows up as posted on 12:00PM, is there any way to fix it so it shows up as 1:00PM? Is there a function to add an hour to whatever the time is? I am clueless here - please help.

leatherback

Hi,

this is from the date section in the php manual (http://www.phpbuilder.com/manual/function.date.php):

$nextyear = mktime (0,0,0,date("m"), date("d"), date("Y")+1)

Sharif

So should I change my code to this:

INSERT INTO ... , date=now(mktime (0,0,0,date("m"), date("d"), date("Y")+1))";

Weedpacket

If you're wanting to add one year (Don't just mindlessly copy what other people type: think about how it applies to you). Besides, that's in PHP, and it looks like you want to do it in MySQL. So, from looking at the date and time functions...

adddate(now(),interval 1 hour)

might be closer to what you want than just "now()".

Sharif

Yea I was looking at that code and thinking why I would want a +1 next to year :-D. So, the code you gave me, it will add ONE hour to current time right?

Weedpacket

Yep; adddate is a MySQL function, so that goes into your query where it says "now()" now.

Of course, if your server shifts time zones in the future you'll have to update this bit 🙂