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.

Share:
210,098

Related videos on Youtube

gautamlakum
Author by

gautamlakum

I help startups and enterprises go ahead with their digital products. Product Designer. Design Sprint Facilitator.

Updated on July 08, 2022

Comments

  • gautamlakum
    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 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')
    
    • Your Common Sense
      Your Common Sense over 12 years
      What timestamp you're talking about? Where did you get it?
  • jimy
    jimy over 12 years
    but the value you get in db shows the column type is datetime, if you want to change it to timestamp then change the column type to varchar(15)
  • Carlos Campderrós
    Carlos Campderrós over 12 years
    Then 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
    gautamlakum over 12 years
    so what is the use of timestamp datatype? s_time field has timestamp datatype. Can't I save 1299762201428 to that field?
  • RollingBoy
    RollingBoy over 12 years
    @lakum4stackof: For timestamp datatype, please refer to dev.mysql.com/doc/refman/5.0/en/timestamp.html for detail.
  • Charles
    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.
    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
    RollingBoy over 12 years
    Saving 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
    charliefortune almost 9 years
    This would store the current timestamp only, not strictly what OP is asking for.
  • BradChesney79
    BradChesney79 over 8 years
    My 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
    Yuri over 7 years
    Why is this better than date('Y-m-d H:i:s','1299762201428')?
  • Richard Tuin
    Richard Tuin over 7 years
    Type safety: FROM_UNIXTIME yields a native mysql date type while php's date() returns a string.
  • Ostico
    Ostico over 6 years
    It'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

Related