CODEWITHSUNDEEP
phpmysqlsqltimestamp
Jan Wy
Jan Wy

Reputation: 1569

Why can't I use php time() for a timestamp column?

I have a timestamp column in a db table. Saving values with:

UPDATE `table` SET `activated_at` = CURRENT_TIMESTAMP WHERE `id` = 123;

works fine.

But when I use the php function time() to get the timestamp, it doesn't work. It only works using date('Y-m-d H:i:s') for the column value. Question is why?

the column definition is:

`activated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

I'm using the php mysql extension (and yes, I know it's deprecated, but I have to maintain some legacy code)

Upvotes: 0

Views: 790

Answers (4)

CreatoR
CreatoR

Reputation: 1652

Look here

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone system variable. For more information, see Section 10.6, “MySQL Server Time Zone Support”

*nix timestamp and MySQL TIMESTAMP is not the same... that's why you need convert from *nix timestamp to MySQL timestamp over date('Y-m-d H:i:s', $unix_timestamp)

Upvotes: 0

웃웃웃웃웃
웃웃웃웃웃

Reputation: 11984

Since time() in php return a unix timestamp and timestamp is a datetime type and it requeires a valid datetime value.If you didnt give a valid datetime value it will be storing like 0000-00-00 00:00:00. If you want to keep your field as datetime type then you must give date('Y-m-d H:i:s') in php

Also check the documention of various date time types in mysql

Upvotes: 3

Nader A. Jabbar
Nader A. Jabbar

Reputation: 3266

if you want to use time() change the column type to varchar(15)

Upvotes: -1

Explosion Pills
Explosion Pills

Reputation: 191749

time() returns a unix timestamp, but the MySQL timestamp column supports specific formats such as YYYY-MM-DD HH:II:SS. You can easily do the conversion using date for PHP or FROM_UNIXTIME in mysql.

Upvotes: 2

Related Questions