使用 php 在 mysql 表中保存时间戳

我在 MySQL 表中有一个字段，它具有 timestamp数据类型。我正在将数据保存到那张表中。但是，当我将时间戳(1299762201428)传递给记录时，它会自动将值 0000-00-00 00:00:00保存到该表中。

如何在 MySQL 表中存储时间戳？

以下是我的 INSERT声明:

INSERT INTO table_name (id,d_id,l_id,connection,s_time,upload_items_count,download_items_count,t_time,status)
VALUES (1,5,9,'2',1299762201428,5,10,20,'1'),
(2,5,9,'2',1299762201428,5,10,20,'1')
216937 次浏览
小开
最佳答案

pass like this

date('Y-m-d H:i:s','1299762201428')
小开

If the timestamp is the current time, you could use the mysql NOW() function

小开

Datatype 'bigint unsigned' may suit this requirement.

小开

I'm guessing that the field you are trying to save the value in is a datetime field it's not but the same seems to be true for timestamps. If so mysql expects the format to be Year-month-day Hour:minute:second. In order to save the timestamp you will have to convert the field to numeric using a query like

alter table <table_name> change <field> <field> bigint unsigned

If you are using the current time you can use now() or current_timestamp.

小开

Hey there, use the FROM_UNIXTIME() function for this.

Like this:

INSERT INTO table_name
(id,d_id,l_id,connection,s_time,upload_items_count,download_items_count,t_time,status)
VALUES
(1,5,9,'2',FROM_UNIXTIME(1299762201428),5,10,20,'1'),
(2,5,9,'2',FROM_UNIXTIME(1299762201428),5,10,20,'1')
小开

Use FROM_UNIXTIME().

Note: 1299762201428 looks more like a millisecond-timestamp (like Date()*1 in JavaScript), and you probably have to divide that by 1000.

小开

Use datetime field type. It comes with many advantages like human readability (nobody reads timestamps) and MySQL functions.

To convert from a unix timestamp, you can use MySQL function FROM_UNIXTIME(1299762201428). To convert back you can use UNIX_TIMESTAMP: SELECT UNIX_TIMESTAMP(t_time) FROM table_name.

Of course, if you don't like MySQL function, you could always use PHP: 'INSERT INTO table_name SET t_time = ' . date('Y-m-d H:i:s', $unix_timestamp).

小开

Better is use datatype varchar(15).

小开

Check field type in table just save time stamp value in datatype like bigint etc.

Not datetime type

小开

Some things to clarify:

  • MySQL timestamp field type doesn't store unix timestamps but rather a datetime-kind value.
  • UNIX timestamp is a number of a regular int type.
  • The timestamp you're talking about is not a regular unix timestamp but a timestamp with milliseconds.

therefore the correct answer would be 

$timestamp = '1299762201428';
$date = date('Y-m-d H:i:s', substr($timestamp, 0, -3));
小开

This should do it:

  $time = new DateTime;
小开

You can use now() as well in your query, i.e. :

insert into table (time) values(now());

It will use the current timestamp.

小开 
$created_date = date("Y-m-d H:i:s");
$sql = "INSERT INTO $tbl_name(created_date)VALUES('$created_date')";
$result = mysql_query($sql);
小开

If I know the database is MySQL, I'll use the NOW() function like this:

INSERT INTO table_name
(id, name, created_at)
VALUES
(1, 'Gordon', NOW())
小开

You can do: $date = \gmdate(\DATE_ISO8601);.


提交答案