-1

I have inserted timestamp in my table using two methods: 1 - time() php code 2 - insert into mytable (...,....,time) values(...,...,CURRENT_TIMESTAMP()). Inserted values are: 1) 1495353761 and 2)2147483647. The type of time in DB is INT(11). My Question is why this is happening and what should I do? (host: localhost)

Meysam Valueian
  • 661
  • 1
  • 5
  • 21
  • Can u post the format of the php timestamp...?? – Pradeep May 23 '17 at 11:27
  • it is a number. int(11) – Meysam Valueian May 23 '17 at 11:30
  • Sometimes it varies with the geographic location. Set the date_default_timezone_set() in ur PHP to get the timestamp of ur location. OR `If u think timestamp should be same, they'll be never same, timestamp changes every milli second` – phpfresher May 23 '17 at 11:30
  • I know about changes in time-stamp. time zone differs 24 hours in maximum. Difference is about 15 years in my case – Meysam Valueian May 23 '17 at 11:32
  • 1
    [Your answer is here...](https://stackoverflow.com/a/17783287/1415724) and I quote that answer: *"2147483647 is the largest int value for mysql. Just change the type from int to bigint."* - @ManManam Let me know if you want me to post my comment as an answer. – Funk Forty Niner May 23 '17 at 11:44
  • @ManManam you're not responding to ^ - you will need to ping me back, I won't look at this thread all day waiting for a response. – Funk Forty Niner May 23 '17 at 11:51
  • Possible duplicate of [Incorrect Integer (2147483647) is inserted into MySQL?](https://stackoverflow.com/questions/10255724/incorrect-integer-2147483647-is-inserted-into-mysql) – Funk Forty Niner May 23 '17 at 11:52
  • @Fred-ii- Thank You For Your Answer. You are right. I have changed the type to BIGINT(20). It returned 20170523021022. If you look at the number it is the concat of date and time. So I user UNIX_TIMESTAMP(). It resolved my problem. – Meysam Valueian May 23 '17 at 11:58
  • @ManManam welcome; I posted my answer below that can be marked as solved. – Funk Forty Niner May 23 '17 at 11:59
  • Possible duplicate of [Incorrect Integer (2147483647) is inserted into MySQL?](https://stackoverflow.com/questions/10255724/incorrect-integer-2147483647-is-inserted-into-mysql) – rmc00 May 23 '17 at 14:03
  • @Fred-ii- The problem was using `CURRENT_TIMESTAMP` for a column of type `INT(11)` . We should use `UNIX_TIMESTAMP`. If we use `UNIX_TIMESTAMP` instead of `CURRENT_TIMESTAMP`, it works even for `INT(11)` type. – Meysam Valueian May 24 '17 at 10:54
  • @ManManam it's usually best to use mysql's datetime types rather than int's and you stand at being faced with the UNIX 2038 bug later on. https://stackoverflow.com/questions/2012589/php-mysql-year-2038-bug-what-is-it-how-to-solve-it – Funk Forty Niner May 24 '17 at 11:00

3 Answers3

0

2147483647 one is wrong. It's equivalent to Tue, 19 Jan 2038 03:14:07 GMT

Refer to https://en.wikipedia.org/wiki/2,147,483,647

You send something wrong. Double check your type of time field.

Koray Küpe
  • 716
  • 6
  • 24
  • Thank you for your answer. As you see there is no parameter. I jast have inserted a row in a query using phpmyadmin. method is `CURRENT_TIMESTAMP()` which returns current timestamp – Meysam Valueian May 23 '17 at 11:35
  • What is type of your time field? e.g: It works with DATETIME. – Koray Küpe May 23 '17 at 11:37
0

Edit: I changed my answer to a community wiki.

2147483647 is the largest int value for mysql. Just change the type from int to bigint.

Pulled/quoted from this answer:

From comments:

"@Fred-ii- Thank You For Your Answer. You are right. I have changed the type to BIGINT(20). It returned 20170523021022. If you look at the number it is the concat of date and time. So I user UNIX_TIMESTAMP(). It resolved my problem. – Man Manam"

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
0

The type of time is int(11) in my table. CURRENT_TIMESTAMP works for DATETIME type. If you want an Integer as timestamp, you should use UNIX_TIMESTAMP().

Meysam Valueian
  • 661
  • 1
  • 5
  • 21