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, Apr 12 '11 at 09:05

score 177 · Accepted Answer · answered Apr 12 '11 at 08:55

177

pass like this

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

answered Apr 12 '11 at 08:55

jimy

4,848
3
35
52

7

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)` – jimy Apr 12 '11 at 08:57
3

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) – Carlos Campderrós Apr 12 '11 at 08:58
4

so what is the use of timestamp datatype? s_time field has timestamp datatype. Can't I save 1299762201428 to that field? – gautamlakum Apr 12 '11 at 09:00
@lakum4stackof: For timestamp datatype, please refer to http://dev.mysql.com/doc/refman/5.0/en/timestamp.html for detail. – RollingBoy Apr 12 '11 at 09:03
@lakum, that is correct. And you **should not** save a unix timestamp to the database. The database has *real* datetime fields. – Charles Apr 12 '11 at 09:04
1

@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). – Michael J.V. Apr 12 '11 at 09:06

score 57 · Answer 2 · edited Jan 02 '14 at 13:14

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 02 '14 at 13:14

d.raev

9,216
8
58
79

answered Apr 12 '11 at 09:04

Richard Tuin

4,484
2
19
18

Why is this better than `date('Y-m-d H:i:s','1299762201428')`? – Yuri May 16 '16 at 07:27
6

Type safety: `FROM_UNIXTIME` yields a native mysql date type while php's `date()` returns a string. – Richard Tuin May 20 '16 at 06:19
6

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](http://php.net/manual/en/function.date-timezone-set.php) – Ostico May 04 '17 at 19:34

score 20 · Answer 3 · edited Feb 28 '14 at 11:44

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 '14 at 11:44

Rahil Wazir

10,007
11
42
64

answered Feb 28 '14 at 11:26

Tushar Kumar Kundan

309
2
5

Your Common Sense · Answer 4 · 2017-02-14T12:25:38.913

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 '17 at 12:25

answered Apr 12 '11 at 09:32

Your Common Sense

156,878
40
214
345

score 5 · Answer 5 · edited May 03 '17 at 18:36

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 03 '17 at 18:36

Nisse Engström

4,738
23
27
42

answered Nov 08 '13 at 13:06

Masood Moshref

370
4
9

score 3 · Answer 6 · answered Apr 12 '11 at 09:00

3

Datatype 'bigint unsigned' may suit this requirement.

answered Apr 12 '11 at 09:00

RollingBoy

2,767
1
14
8

Belinda · Answer 7 · 2011-04-12T09:08:49.280

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.

score 1 · Answer 8 · answered Feb 25 '13 at 22:37

1

This should do it:

  $time = new DateTime;

answered Feb 25 '13 at 22:37

misaizdaleka

1,776
3
21
32

score 1 · Answer 9 · answered Apr 12 '11 at 09:06

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 '11 at 09:06

Udo G

12,572
13
56
89

score 1 · Answer 10 · answered Apr 12 '11 at 09:09

1

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

Not datetime type

answered Apr 12 '11 at 09:09

score 0 · Answer 11 · answered Nov 06 '18 at 00:14

0

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

answered Nov 06 '18 at 00:14

HelpNeeder

6,383
24
91
155

score 0 · Answer 12 · edited Apr 12 '11 at 09:02

0

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

edited Apr 12 '11 at 09:02

kapa

77,694
21
158
175

answered Apr 12 '11 at 08:58

ipsum

1,042
7
17

score 0 · Answer 13 · answered Apr 12 '11 at 09:06

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 '11 at 09:06

Markus Hedlund

23,374
22
80
109

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. – Udo G Apr 12 '11 at 09:08
@Udo G: You are correct, but I still prefer `datetime` for various reasons. What is the biggest dis/advantage in your opinion? – Markus Hedlund Apr 12 '11 at 09:24
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. – Udo G Apr 12 '11 at 09:29

score 0 · Answer 14 · answered Apr 12 '11 at 09:06

0

Better is use datatype varchar(15).

answered Apr 12 '11 at 09:06

gautamlakum

11,815
23
67
90

3

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. – RollingBoy Apr 12 '11 at 09:13
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... – BradChesney79 Feb 20 '15 at 10:56
timestamp is int, you should not use varchar for this kind of value – Jamviet.com Jun 10 '22 at 20:40

score -1 · Answer 15 · answered Mar 19 '14 at 12:23

-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 '14 at 12:23

Jannie Theunissen

28,256
21
100
127

This would store the current timestamp only, not strictly what OP is asking for. – charliefortune Jan 14 '15 at 10:12

Saving timestamp in mysql table using php

15 Answers15

Linked