Saving timestamp in mysql table using php
210,098
Solution 1
pass like this
date('Y-m-d H:i:s','1299762201428')
Solution 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')
Solution 3
$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
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
You can use now()
as well in your query, i.e. :
insert into table (time) values(now());
It will use the current timestamp.
Related videos on Youtube
Author by
gautamlakum
I help startups and enterprises go ahead with their digital products. Product Designer. Design Sprint Facilitator.
Updated on July 08, 2022Comments
-
gautamlakum over 1 year
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 value0000-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')
-
Your Common Sense over 12 yearsWhat timestamp you're talking about? Where did you get it?
-
-
jimy over 12 yearsbut the value you get in db shows the column type is
datetime
, if you want to change it totimestamp
then change the column type tovarchar(15)
-
Carlos Campderrós over 12 yearsThen put it on an INT field. Anyway, a timestamp is only a representation of a date, and vice versa. You can convert from timestamp to date with the function jimy told you, and the other way with
strtotime
. edit: btw, timestamp only covers a range of all possible dates (1970-01-01 to xx-xx-2032 I think) -
gautamlakum over 12 yearsso what is the use of timestamp datatype? s_time field has timestamp datatype. Can't I save 1299762201428 to that field?
-
RollingBoy over 12 years@lakum4stackof: For timestamp datatype, please refer to dev.mysql.com/doc/refman/5.0/en/timestamp.html for detail.
-
Charles over 12 years@lakum, that is correct. And you should not save a unix timestamp to the database. The database has real datetime fields.
-
Michael J.V. over 12 years@lakum4stackof - the use of timestamp is that you do save the timestamp, but it is only displayed as a date. Internally (as all data types) it's stored as signed integer. If you also want to format that timestamp as an integer, I suggest that you just use INT field then. Use of timestamp column is the date manpipulation (adding intervals and such).
-
RollingBoy over 12 yearsSaving a number in the form of string is usually not a good pratice, for strings are slower in comparison and harder to perform calculation.
-
charliefortune almost 9 yearsThis would store the current timestamp only, not strictly what OP is asking for.
-
BradChesney79 over 8 yearsMy vote goes to unsigned int-- but let's go down the rabbit hole. Why varchar and not char? It is a fixed length entry-- micro-optimization, but still...
-
Yuri over 7 yearsWhy is this better than
date('Y-m-d H:i:s','1299762201428')
? -
Richard Tuin over 7 yearsType safety:
FROM_UNIXTIME
yields a native mysql date type while php'sdate()
returns a string. -
Ostico over 6 yearsIt's better because web server ( PHP ) and MySQL could be in different places. So, date('Y-m-d H:i:s','1299762201428') will set the TimeZone of the web server. With different web servers placed in different zones you will have inconsistent data. Alternatively, in your code, you have to force the application timezone to the timezone of MySQL. date_timezone_set