MySQL NOW()/LOCALTIME() Not Working

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1172 Views - Last Post: 21 October 2013 - 12:49 PM Rate Topic: -----

#1 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 09:34 AM

Right, so I'm making a query to get the NOW() datetime. The database column is set for datetimes. And wouldn't you know it, that it doesn't grab the time! Sure, my date appears, but not the time.

function addReply() {
		$DBHandle = openDB();
		
		if(!empty($_POST['replyMessage'])) {
			try {
			
				if(empty($_COOKIE['user'])) {
					$userID = 3; 
				} else {
					$TheUser = explode(" ", $_COOKIE['user']);
					$userID = $TheUser[0];
					
					$addPostCount = $DBHandle->prepare('UPDATE users SET postCount = postCount+1 WHERE id = :id');
					$addPostCount->execute(array(':id' => $userID));
				}
				
				$addReply = $DBHandle->prepare('INSERT INTO forumReplies SET replyContent = :content, replyAuthor = :author, replyTopic = :topic, replyDate = LOCALTIME()');
			
				$addReply->execute(array(':content' => $_POST['replyMessage'], ':author' => $userID, ':topic' => $_GET['id']));
					
				$addReplyCount = $DBHandle->prepare('UPDATE forumTopics SET topicReplies = topicReplies+1 WHERE id = :id');
				$addReplyCount->execute(array(':id' => $_GET['id']));
				
				header('Location: http://blahblah.net/forums/viewTopic.php?id=' . $_GET['id'] . '&page=' . $_GET['page']);
				exit;
			} catch(PDOException $ex) {
				die('Posting failure! ' . $ex->getMessage() . " Sorry.");
			}
		} else {
			header('Location: http://blahblah.net/forums/forum.php?do=somethingWentWrong');
			exit;
		}
	}



I tried both NOW() and LOCALTIME(), to no avail. Maybe I'm just going about it wrong?

Is This A Good Question/Topic? 0
  • +

Replies To: MySQL NOW()/LOCALTIME() Not Working

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,828
  • Joined: 12-June 08

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 09:51 AM

Quote

Sure, my date appears, but not the time.


.. is your column actually a date/time, or just date?
Was This Post Helpful? 0
  • +
  • -

#3 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 10:58 AM

I'm absolutely, 100%, completely certain it's datetime.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3734
  • View blog
  • Posts: 13,063
  • Joined: 12-December 12

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:18 AM

Slightly confused: Your posted code uses INSERT and UPDATE statements, but you say the problem you are having is in displaying a value?

Do you mean that when you read the table-data in phpMyAdmin or similar it doesn't show the time?

This post has been edited by andrewsw: 21 October 2013 - 11:23 AM

Was This Post Helpful? 0
  • +
  • -

#5 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,828
  • Joined: 12-June 08

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:44 AM

Quote

I'm absolutely, 100%, completely certain it's datetime.

then something else must be going on.. The working theory is NOW() should work.. so either your table is wrong, you query is busted, or you are mistaking what you are adding to the table when you view it through the admin panel.

17	                $addReply = $DBHandle->prepare('INSERT INTO forumReplies SET replyContent = :content, replyAuthor = :author, replyTopic = :topic, replyDate = LOCALTIME()');


Are you actually seeing only the date in the MYSQL admin area?

Side note - in cases like this I typically set teh column's default value to 'NOW()'.. so I don't need to every worry about needing to call i.t


13	                    $addPostCount = $DBHandle->prepare('UPDATE users SET postCount = postCount+1 WHERE id = :id');


21	                $addReplyCount = $DBHandle->prepare('UPDATE forumTopics SET topicReplies = topicReplies+1 WHERE id = :id');


Peculiar - are you really hard coding the post count? Why not just use a view that gets the "select count()"?
Was This Post Helpful? 0
  • +
  • -

#6 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:45 AM

Well, when it gets inserted it's always set to 00:00:00. When I display it after formatting, it gets 12:00 AM as expected from 00:00:00. When I look at the tables through both Navicat and phpMyAdmin, it also shows 00:00:00.
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,828
  • Joined: 12-June 08

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:49 AM

Quote

. When I look at the tables through both Navicat and phpMyAdmin, it also shows 00:00:00.

Care to drop a screen shot?

You are not doing something screwy with the timezone or date formatting, are you?

Quote

NOW()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS.uuuuuu format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3734
  • View blog
  • Posts: 13,063
  • Joined: 12-December 12

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:49 AM

To test if it is a problem specifically with NOW() I would insert a specific date-time value and see whether the time from this also disappears in phpMyAdmin. I might do this both in code and directly from the the control panel.

This post has been edited by andrewsw: 21 October 2013 - 11:50 AM

Was This Post Helpful? 0
  • +
  • -

#9 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 11:54 AM

Hold up; new thingy goin' on. The times started to pop up after a few more tests, but it still shows 12:00 AM on display... the times weren't changing in the DB before... but anywho, here's the function I use to format my dates (borrowed from a pal, slightly modified)

function betterDate($uglyDate) {
	return date("jS M Y, g:i A", mktime(0,0,0,substr($uglyDate, 5, 2),substr($uglyDate, 8, 2),substr($uglyDate, 0, 4)));
}



Oh, sorry for double post, but I should also mention that I did try changing times before; it reverted back to 00:00:00 each time.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3734
  • View blog
  • Posts: 13,063
  • Joined: 12-December 12

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:04 PM

That function, on a quick test, removes the time:

echo betterDate('2013-10-21 13:22:20');
21st Oct 2013, 12:00 AM

Was This Post Helpful? 0
  • +
  • -

#11 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:06 PM

Oh... oh...
Well, no wonder I only ever used dates...

In any case, I'm completely lost as to what that function does specifically, since my pal never explained it to me. How can I fix it?
Was This Post Helpful? 0
  • +
  • -

#12 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9493
  • View blog
  • Posts: 35,828
  • Joined: 12-June 08

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:12 PM

You fix it by not using it and work out what you want to have for an output.

http://php.net/manua...ormats.date.php
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3734
  • View blog
  • Posts: 13,063
  • Joined: 12-December 12

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:16 PM

I wouldn't use that function, I don't like all that substr() nonsense - it shouldn't be necessary.

Do you need to format the date-time in PHP? You could alternatively return a (string) formatted version of the date directly in the sql. If you are only going to display the date-time, and not perform any date-arithmetic on it, then this would be more efficient, and easier.

MySQL DATE_FORMAT()

This post has been edited by andrewsw: 21 October 2013 - 12:14 PM

Was This Post Helpful? 0
  • +
  • -

#14 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:25 PM

View Postandrewsw, on 21 October 2013 - 12:16 PM, said:

Do you need to format the date-time in PHP? You could alternatively return a (string) formatted version of the date directly in the sql. If you are only going to display the date-time, and not perform any date-arithmetic on it, then this would be more efficient, and easier.

MySQL DATE_FORMAT()


Well, I was planning on 'date-arithmetic' later on, but I suppose you're right for the time being. Thanks, guys!

Just to be sure... DATE_FORMAT(NOW(), '%D %b %y\, %l:%i %p') should work, right? What I'm gathering is to use it to make the date into a string instead of using datetime.
Was This Post Helpful? 0
  • +
  • -

#15 iLiTH  Icon User is offline

  • D.I.C Head

Reputation: 7
  • View blog
  • Posts: 145
  • Joined: 25-August 13

Re: MySQL NOW()/LOCALTIME() Not Working

Posted 21 October 2013 - 12:39 PM

~UPDATE~
Alright, so I would edit my first post with the fix, but it's not letting me so here goes;

I changed the function to this:
function betterDate($uglyDate) {
	$date = new DateTime($uglyDate);
	return $date->format("jS M Y, g:i A");
}


Since it would seem that PHP's date() doesn't like to format anything not an integer-timestamp. This one works perfectly.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2