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')
This question is related to
php
mysql
database
sql-insert
pass like this
date('Y-m-d H:i:s','1299762201428')
You can do: $date = \gmdate(\DATE_ISO8601);
.
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.
$created_date = date("Y-m-d H:i:s");
$sql = "INSERT INTO $tbl_name(created_date)VALUES('$created_date')";
$result = mysql_query($sql);
Check field type in table just save time stamp value in datatype like bigint
etc.
Not datetime
type
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)
.
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')
This should do it:
$time = new DateTime;
Some things to clarify:
therefore the correct answer would be
$timestamp = '1299762201428';
$date = date('Y-m-d H:i:s', substr($timestamp, 0, -3));
Better is use datatype varchar(15)
.
If the timestamp is the current time, you could use the mysql NOW()
function
Datatype 'bigint unsigned' may suit this requirement.
You can use now()
as well in your query, i.e. :
insert into table (time) values(now());
It will use the current timestamp.
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())
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.
Source: Stackoverflow.com