Saving timestamp in mysql table using php

问题:

I have a field in a MySQL table which has a timestamp data type. I am saving data into that table. But when I pass the timestamp (1299762201428) to the record, it automatically saves the value 0000-00-00 00:00:00 into that table.

How can I store the timestamp in a MySQL table?

Here is my INSERT statement:

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')

回答1:

pass like this

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

回答2:

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')

回答3:

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

回答4:

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));

回答5:

Datatype 'bigint unsigned' may suit this requirement.

回答6:

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.

回答7:

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.

回答8:

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

Not datetime type

回答9:

This should do it:

  $time = new DateTime; 

回答10:

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

回答11:

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).

回答12:

Better is use datatype varchar(15).

回答13:

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

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

It will use the current timestamp.

回答14:

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

回答15:

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())