Saving timestamp in mysql table using php
PhpMysqlDatabaseSql InsertPhp Problem Overview
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')
Php Solutions
Solution 1 - Php
pass like this
date('Y-m-d H:i:s','1299762201428')
Solution 2 - Php
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')
Solution 3 - Php
$created_date = date("Y-m-d H:i:s");
$sql = "INSERT INTO $tbl_name(created_date)VALUES('$created_date')";
$result = mysql_query($sql);
Solution 4 - Php
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));
Solution 5 - Php
You can use now() as well in your query, i.e. :
insert into table (time) values(now());
It will use the current timestamp.
Solution 6 - Php
Datatype 'bigint unsigned' may suit this requirement.
Solution 7 - Php
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.
Solution 8 - Php
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.
Solution 9 - Php
Check field type in table just save time stamp value in datatype like bigint etc.
Not datetime type
Solution 10 - Php
This should do it:
$time = new DateTime;
Solution 11 - Php
If the timestamp is the current time, you could use the mysql NOW() function
Solution 12 - Php
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).
Solution 13 - Php
Better is use datatype varchar(15).
Solution 14 - Php
You can do: $date = \gmdate(\DATE_ISO8601);.
Solution 15 - Php
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())