0

I need to save the value returned by the php "time();" function in a mysql table. When I create an int field within a table, the maximum length of the int value is requested. I wanted to ask you what value suggest to include in the type declaration of int.

I ask you this because, by saving the digit in int, it will be easy to compare it with other results of the "time ();" (which if I understand correctly, it always returns an int value).

Thank you

Vallo
  • 23
  • 6
  • 1
    [MySQL has few int types](https://dev.mysql.com/doc/refman/8.0/en/integer-types.html). To accommodate UNIX timestamp you need at least INT, after 2030 maybe even more. – Dharman Apr 30 '19 at 22:34
  • 1
    [Should I use the datetime or timestamp data type in MySQL?](https://stackoverflow.com/questions/409286/should-i-use-the-datetime-or-timestamp-data-type-in-mysql) – Dharman Apr 30 '19 at 22:38
  • 1
    What are you planning on using the UNIX timestamp for? Is it just to store arbitrary dates in DB? There is a rather narrow band of use cases for storing UNIX timestamps in DB as integers. – Dharman Apr 30 '19 at 22:41

1 Answers1

2

As of PHP manual:

Returns the current time measured in the number of seconds since the Unix Epoch (January 1 1970 00:00:00 GMT).

So you're better of with using DATETIME type in your MySQL database and saving the timestamp after you convert it into valid format. For that I'd use DateTime. Even better if you just use new DateTime instead of time(), as DateTime takes time in constructor, which by default is now:

public DateTime::__construct ([ string $time = "now" [, DateTimeZone $timezone = NULL ]] )

If you were to use Doctrine 2, it would have been enough. Otherwise, you must format your DateTime object before inserting:

$dt = new DateTime();
$dt->format('Y-m-d H:i:s');

This will produce something like

2019-04-30 15:34:16

which is completely valid format for your DATETIME type in your mysql database.

If you need to compare other DateTime objects, you can always use DateTime::diff:

public DateTime::diff ( DateTimeInterface $datetime2 [, bool $absolute = FALSE ] ) : DateInterval

This will be enough for smart and representable operations with DateTimes and their comparison.

EDIT:

As you wrote

Ok, I converted the db field into a "DATETIME" field. But now I can't compare the result of the table field with a DateTime () object. Do you know how I can convert the table field to be comparable with a DateTime () object?

You need to create the DateTime object from the data you have in your database. It is achieved by using createFromFormat:

public static DateTime::createFromFormat ( string $format , string $time [, DateTimeZone $timezone ] ) : DateTime

So what you do is:

$dt = DateTime::createFromFormat('Y-m-d H:i:s', '2019-04-30 15:34:16');

and voila!

Kevin Kopf
  • 13,327
  • 14
  • 49
  • 66
  • I agree that DateTime is better to store dates, but OP is asking about storing UNIX timestamp specifically. MySQL has timestamp type too: https://dev.mysql.com/doc/refman/5.5/en/datetime.html – Dharman Apr 30 '19 at 22:36
  • 1
    @Dharman OP is asking about comparing things, he just doesn't know he can compare DateTime objects yet. – Kevin Kopf Apr 30 '19 at 22:38
  • `DateTime::diff` will get you the difference between 2 dates. You can compare the objects directly with each other. e.g. `if($dt < $dt2)` – Dharman Apr 30 '19 at 22:46
  • @AlexKarshin Ok, I converted the db field into a "DATETIME" field. But now I can't compare the result of the table field with a DateTime () object. Do you know how I can convert the table field to be comparable with a DateTime () object? – Vallo May 01 '19 at 07:23