Save time() as INTEGER timestamp in MySQL via PDO as phpMyAdmin says `timestamp` is number

Question

How can I send timestamps as integer number to MySQL via PDO in PHP? I dont like to send time as string, I want to send them as pure integer (as UNIX TIME).

This is my code but it saves ZERO timestamp:

$sql = 'REPLACE INTO table_name (id,sent_timestamp) VALUES (:id,:sent_timestamp);';
$stmt =   $pdo->prepare($sql);

$stmt->bindValue(':id',$id,PDO::PARAM_INT);
$stmt->bindValue(':sent_timestamp',time(),PDO::PARAM_INT); // I define this column in phpMyadmin as TIMESTAMP type.

$stmt->execute();

id is unique and sql query is working but timestamp save as ZERO. I also replaced time() with arbitrary number like 123456 but it was also saved in DB as zero.

UPDATED: due to some more research and with thanks to @Hanky Panky , it is somthing confusing: why phpMyadmin says it stores timestamp as the numbers of seconds BUT we couldn't send timestamp as integer. Is there any reference to this item that we cant send in integer form?

@Hanky Panky , you marked my question as an exact duplicate but that question neither similar to my question nor comply my needs. Please remove this duplication mark. — Seyfi, Jul 09 '16 at 16:40
It is. You just need to change your data type to an `INT(11)` from `TIMESTAMP` as the format for `DATETIME/TIMESTAMP` is different and can not store an integer unix timestamp value. That is what is essentially discussed in that post. It has nothing to do with PDO. I explained that in an answer as well but i removed my answer when i saw a post discussing that already. — Hanky Panky, Jul 09 '16 at 16:41
This change solves problem but I liked to see some refernece to documentation that says it is imposible,something that I couldn't find in that question. phpMyadmin says sql saves `timestamp` in numbers that i show in image ,so maybe rare way is there that we do not know about. — Seyfi, Jul 09 '16 at 16:47
What reference would be needed to understand that a number can not be stored in a field that is of format `YYYY-MM-DD-HH:MM:SS`? It is just like asking why you cant store `Hello` in a numeric field — Hanky Panky, Jul 09 '16 at 16:49
So why phpMyadmin says `...store as the numbers of seconds....`? Not says `formatted time`. — Seyfi, Jul 09 '16 at 16:53
Their wording is a confusing attempt at explaining that the date time stored in a `TIMESTAMP` field will always contain UTC time and not whatever the time is at the system right now. That is explained in the manual as well. *MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time.* — Hanky Panky, Jul 09 '16 at 16:58
I like to change my question title. Maybe some one knows the real answer why they name in that way. Can I? I ask you as you have more experience in SO. — Seyfi, Jul 09 '16 at 17:00
I have removed my vote as i do not want to discourage you from learning what you want to learn, but i still believe its just a misunderstanding of their terminology and nothing more. You can edit your question as you please — Hanky Panky, Jul 09 '16 at 17:02
It stores a timestamp __based__ on the number of seconds since 1970. It marks an absolute point in time. Like ... when we make a phone or Skype call at 9am based on a TIMESTAMP, no matter where you live in the world, we will come online at same time. With DATETIME we would be online hours before or after each other ... or in this case depending on your servers time and my servers timezone. The wording might be confusing was mentioned by Hanky Panky. — nimmneun, Jul 09 '16 at 17:29
@HankyPanky, Finally I found the answer, long words short: we can send integers but only that integers in YYYYMMDDHHMMSS or YYMMDDHHMMSS format, only the numbers makes sense as a date not every integers like unix-time integers. You can see the full answer if you like. — Seyfi, Jul 09 '16 at 18:18
@RyanVincent so I ONLY can save as int not `timestamp` at all. (for unix-time seconds) — Seyfi, Jul 09 '16 at 18:28
Sorry, I confused you - no - Timestamp columns only accept DateTime formats. **They are DateTime fields** with 'timestamp' behaviour - that is 1) Stored as UTC. Display as the current server timezone. Maybe useful? http://www.mysqltutorial.org/mysql-timestamp.aspx — Ryan Vincent, Jul 09 '16 at 18:33
@TickTock Take a look at my answer. The answer to this is simple an clean. `time()` needs to be converted to `Y-m-d H:i:s` to store as a timestamp in mysql — Ikhlak S., Jul 09 '16 at 18:50
You can use PHP `time()` in your PHP SQL queries but you have to use [FROM_UNIXTIME(unix_timestamp), FROM_UNIXTIME(unix_timestamp,format)](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime) in your SQL query. These will convert the `unix epoch seconds` to UTC, using your server timezone. — Ryan Vincent, Jul 09 '16 at 19:11
imo, It maybe easier to just use `DateTime` columns and look after the time-zone stuff yourself? We do this all the time anyway? Use 'timestamp' for stuff the server needs to record - i.e. date of creation and update etc. The user time stuff - you look after yourself? — Ryan Vincent, Jul 09 '16 at 19:16

score 2 · Answer 1 · answered Jul 09 '16 at 17:24

A TIMESTAMP in MySQL is a string in the format YYYY-MM-DD HH:MM:SS[.fraction]. The optional fraction part allows to store values to a precision of microseconds. The allowed range for TIMESTAMP values is 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999.

In your query, you are trying to store an integer value into a TIMESTAMP field. For it to work properly, you must send a valid TIMESTAMP value:

$date = new Datetime('now');
$stmt->bindValue(':sent_timestamp', $date->format('Y-m-d H:i:s'), PDO::PARAM_STRING);

What phpmyadmin tries to explain is that TIMESTAMP values are internally stored as integers. But what matters to us, PHP programmers, is that TIMESTAMP values are seen as strings, as described above.

Remember that the authoritative source for MySQL is the MySQL manual, not explanations given by phpmyadmin.

So you say that it is IMPOSSIBLE to send directly `integer` numbers to `timstamp` type field due to `MySQL` documentation? — Seyfi, Jul 09 '16 at 17:38

Seyfi · Accepted Answer · 2016-07-09T19:25:00.657

with special THANKS to whom send answers but unfortunately non of them was the answer of my question. Hopefully, after hours of searching I found the answer:

We could send TIMESTAMP as an integer number. We could send it in YYYYMMDDHHMMSS format and some other format. Invalid times interpret as ZEORs. so this code works flawlessly:

$stmt->bindValue(':sent_timestamp',20160709120000,PDO::PARAM_INT); //2016-07-09-12:00:00

MySQL Refernce says:

MySQL recognizes DATETIME and TIMESTAMP values in these formats:

As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.

so due to this documentation it is IMPOSSIBLE to send unix-time type integers-for example 14973898 (seconds after 1970-01-01-00-00) to TIMESTAMPtype fields BUT it is POSSIBLE to send integers in YYYYMMDDHHMMSS and YYMMDDHHMMSS formats ( like 20160709120000 that represents 2016-07-09-12:00:00)

UPDATED: my mistake on digits, it should 14-digit that i mistakely was wroten 12 digits by mistake.

I couldn't understand! why this answer should get Down vote?! — Seyfi, Jul 09 '16 at 18:41
It seems to are missing the last bit seconds(`ss`). Other wise this is the correct answer! +1 — Ikhlak S., Jul 09 '16 at 19:19
@RyanVincent you are true an i upvoted you formerley. I voted your second one up too — Seyfi, Jul 09 '16 at 19:32

score 0 · Answer 3 · edited May 23 '17 at 11:51

0

take a look at the answer using FROM_UNIXTIME()

saving-timestamp-in-mysql-table-using-php

edited May 23 '17 at 11:51

Community

1
1

answered Jul 09 '16 at 17:25

Ruben Pirotte

386
2
11

Thank you but it is about how to send timestamp. my question is about how directly send integers to timestamp field. – Seyfi Jul 09 '16 at 17:40
why do you want to send it as an integer? – Ruben Pirotte Jul 09 '16 at 17:56
I really found the answer, you can see it. – Seyfi Jul 09 '16 at 18:23

score 0 · Answer 4 · answered Jul 09 '16 at 18:04

0

Hope this solves your problem. Thanks

answered Jul 09 '16 at 18:04

Saibal Roy

413
2
5

thanks , but its how we save ints ,You can see the real answer that I found after hours of frustrating research. – Seyfi Jul 09 '16 at 18:15
1

Yes. Sometimes it pays you in the long run to use shortcut practical tricks that solves your problem. Thanks. Hope you have now the work being done. Thanks. :) – Saibal Roy Jul 09 '16 at 18:37
Yes and i use inetegers for my field type.Albeit this question is about confusing words that phpMyadmin uses and `TIMESTAMP` integers!Thanks – Seyfi Jul 09 '16 at 18:40
1

@EpochGalaxy Next time please provide a proper answer with text explaining a bit instead of just posting pictures – Ikhlak S. Jul 09 '16 at 18:55

Ikhlak S. · Answer 5 · 2016-07-10T07:37:45.110

0

Mysql timestamp requires the stamp format to be 'Y-m-d H:i:s'.

You might be getting 0000 since to you telling PDO to store it as a INT. Remove the PDO::PARAM_INT from the bindValue statment

So, you would store time() as a timestamp like this:

$stmt->bindValue(':sent_timestamp',date("Y-m-d H:i:s", time()));

edited Jul 10 '16 at 07:37

answered Jul 09 '16 at 18:32

Ikhlak S.

8,578
10
57
77

+1 yes it is YYYYMMDDHHMMSS format. i like to send direct integers that is only possible in that format. look to above answer – Seyfi Jul 09 '16 at 18:50
@TickTock How did you end up with this unfamiliar format `201607091200`? – Ikhlak S. Jul 09 '16 at 18:57
If you look to my answer you completely would get the answer. 201607091200 is 2016-07-09-12:00. We can send integers but only that make sense a date. I do not why some one downvotes my answer? what is the problem with it. If you know please tell me. – Seyfi Jul 09 '16 at 19:00
1

@TickTock Your answer is right, but you are providing an incorrect value. You are supposed to be providing 14 digits instead of 12. You are missing the last `ss` – Ikhlak S. Jul 09 '16 at 19:17
My Mistake ,UPDATED – Seyfi Jul 09 '16 at 19:21
@TickTock Please select an best answer so this question can be marked as answered – Ikhlak S. Jul 09 '16 at 19:26
I like but I could not – Seyfi Jul 09 '16 at 19:32
@TickTock And why is that? – Ikhlak S. Jul 09 '16 at 19:34
Question maker and the best answer is from same person. – Seyfi Jul 09 '16 at 19:35
@TickTock Yes, so mark your answer as the best answer – Ikhlak S. Jul 09 '16 at 19:37
1

I will, StackOverflow says I can after two days( dont no why) I liked to some one else bring such an answer and get tick from me and save my time but non of them can convince me about integers and so on. +1 for your hint about my mistake on digits. – Seyfi Jul 09 '16 at 19:43

Save time() as INTEGER timestamp in MySQL via PDO as phpMyAdmin says `timestamp` is number

5 Answers5