CODEWITHSUNDEEP
mysqldatetimetime
daGrevis
daGrevis

Reputation: 21333

MySQL NOW() Returns Only Year

MySQL docs about NOW() function...

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.

Here's my query...

$sql = "

    INSERT INTO `users` ( `username`, `password`, `email`, `time` )
    VALUES ('{$username}', '" . sha1( $password ) . "', '{$email}', NOW() )

";

The problem is that in the database I don't have the full datetime, but only the year. Any solutions?

Upvotes: 2

Views: 1995

Answers (3)

YTEpisodes
YTEpisodes

Reputation: 41

I had this same trouble and was stumped for the longest time till I read this question. I used mysql's now() function to update a member's last login time.

MySQL's now() returns the date time as a string, like YYYY-MM-DD HH:MM:SS - somewhere along the line I stopped using this and started using time() (UNIXTIME) (makes it easier to work with) which returns a string of numbers, like 1352254528. But you can't store a string of numbers like this in a datetime field, and likewise, you can't store YYYY-MM-DD HH:MM:SS in an int(11) field. The latter will result in only the year being stored.

In the end you should use:

int(11) if you're going to use PHP's time() function to store the time as a string of numbers or DATETIME field if you're going to use MySQL's NOW() function.

Upvotes: 4

krtek
krtek

Reputation: 26597

try 

strtotime(now())

And you should definitely use timestamp or datetime as a type for your column.

Upvotes: 1

Pekka
Pekka

Reputation: 449525

It's int(10) like it was when I used UNIX timestamp.

NOW() works for DATETIME fields only.

You need to either convert your field to DATETIME (the preferable method), or convert NOW() into a UNIX timestamp using UNIX_TIMESTAMP():

UNIX_TIMESTAMP(NOW())

Upvotes: 5

Related Questions