Saving timestamp in mysql table using php

Question

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

What timestamp you're talking about? Where did you get it? — Your Common Sense
– Your Common Sense, Commented Apr 12, 2011 at 9:05

jimy · Accepted Answer · 2011-04-12 08:55:36Z

180

pass like this

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

answered Apr 12, 2011 at 8:55

jimy

4,9183 gold badges37 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

jimy Over a year ago

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 Over a year ago

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 Over a year ago

so what is the use of timestamp datatype? s_time field has timestamp datatype. Can't I save 1299762201428 to that field?

RollingBoy Over a year ago

@lakum4stackof: For timestamp datatype, please refer to dev.mysql.com/doc/refman/5.0/en/timestamp.html for detail.

Michael J.V. Over a year ago

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

|

d.raev · Accepted Answer · 2014-01-02 13:14:41Z

57

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

edited Jan 2, 2014 at 13:14

d.raev

9,5848 gold badges63 silver badges84 bronze badges

answered Apr 12, 2011 at 9:04

Richard Tuin

4,5722 gold badges23 silver badges18 bronze badges

3 Comments

Yuri Over a year ago

Why is this better than date('Y-m-d H:i:s','1299762201428')?

Richard Tuin Over a year ago

Type safety: FROM_UNIXTIME yields a native mysql date type while php's date() returns a string.

Ostico Over a year ago

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

Rahil Wazir · Accepted Answer · 2014-02-28 11:44:19Z

20

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

edited Feb 28, 2014 at 11:44

Rahil Wazir

10.1k11 gold badges46 silver badges66 bronze badges

answered Feb 28, 2014 at 11:26

Tushar Kumar Kundan

3092 silver badges5 bronze badges

Comments

Your Common Sense · Accepted Answer · 2017-02-14 12:25:38Z

16

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

edited Feb 14, 2017 at 12:25

answered Apr 12, 2011 at 9:32

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Comments

Nisse Engström · Accepted Answer · 2017-05-03 18:36:54Z

5

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

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

It will use the current timestamp.

edited May 3, 2017 at 18:36

Nisse Engström

4,76323 gold badges29 silver badges44 bronze badges

answered Nov 8, 2013 at 13:06

Masood Moshref

3804 silver badges9 bronze badges

Comments

RollingBoy · Accepted Answer · 2011-04-12 09:00:11Z

3

Datatype 'bigint unsigned' may suit this requirement.

answered Apr 12, 2011 at 9:00

RollingBoy

2,8171 gold badge16 silver badges8 bronze badges

Comments

Belinda · Accepted Answer · 2011-04-12 09:08:49Z

2

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.

edited Apr 12, 2011 at 9:08

answered Apr 12, 2011 at 9:03

Belinda

1,2272 gold badges14 silver badges25 bronze badges

Comments

Udo G · Accepted Answer · 2011-04-12 09:06:24Z

1

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.

answered Apr 12, 2011 at 9:06

Udo G

13.2k16 gold badges64 silver badges94 bronze badges

Comments

user319198 · Accepted Answer · 2011-04-12 09:09:08Z

1

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

Not datetime type

answered Apr 12, 2011 at 9:09

user319198

Comments

misaizdaleka · Accepted Answer · 2013-02-25 22:37:30Z

1

This should do it:

  $time = new DateTime;

answered Feb 25, 2013 at 22:37

misaizdaleka

1,8263 gold badges22 silver badges32 bronze badges

Comments

kapa · Accepted Answer · 2011-04-12 09:02:34Z

0

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

edited Apr 12, 2011 at 9:02

kapa

78.9k21 gold badges166 silver badges178 bronze badges

answered Apr 12, 2011 at 8:58

ipsum

1,0627 silver badges17 bronze badges

Comments

Markus Hedlund · Accepted Answer · 2011-04-12 09:06:34Z

0

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

answered Apr 12, 2011 at 9:06

Markus Hedlund

24.4k22 gold badges82 silver badges113 bronze badges

3 Comments

Udo G Over a year ago

The timestamp field type is human readable too (when using SELECT in the console). They are very different and both have their dis/advantages. The main difference is how time zones are being handled.

Markus Hedlund Over a year ago

@Udo G: You are correct, but I still prefer datetime for various reasons. What is the biggest dis/advantage in your opinion?

Udo G Over a year ago

biggest advantage for timestamp: it better matches the PHP timestamps as it is immune to time zone settings of both the server and the client, whereas datetime changes it's displayed value depending on the time zone of your MySQL client (of course it depends on your project, which one is better). biggest disadvantage for timestamp: it does not support NULL values and (I don't know what they did smoke when they programmed this) a TIMESTAMP NOT NULL field becomes a TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP. In my case I unfortunately need to use TIMESTAMP for TZ reasons.

gautamlakum · Accepted Answer · 2011-04-12 09:06:58Z

0

Better is use datatype varchar(15).

answered Apr 12, 2011 at 9:06

gautamlakum

12.1k23 gold badges70 silver badges90 bronze badges

3 Comments

RollingBoy Over a year ago

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.

BradChesney79 Over a year ago

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

Jamviet.com Over a year ago

timestamp is int, you should not use varchar for this kind of value

HelpNeeder · Accepted Answer · 2018-11-06 00:14:28Z

0

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

answered Nov 6, 2018 at 0:14

HelpNeeder

6,52026 gold badges95 silver badges160 bronze badges

Comments

Jannie Theunissen · Accepted Answer · 2014-03-19 12:23:48Z

-1

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

answered Mar 19, 2014 at 12:23

Jannie Theunissen

30.3k21 gold badges110 silver badges136 bronze badges

1 Comment

charliefortune Over a year ago

This would store the current timestamp only, not strictly what OP is asking for.

Collectives™ on Stack Overflow

Saving timestamp in mysql table using php

15 Answers 15

6 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

15 Answers 15

6 Comments

3 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related