Best practice to manage date and days

Question

I am working on a project where I have many operations to manage. Each operation have an end date and is composed by a certain amount of tasks.

I want to display reminders (a text displayed on the screen) if a task is not done before [end date] - X days.

All the data is stored in MySQL database and I work with PHP and HTML5.

Which datatype is (are) the best to work with date and days (to perform calculations)?
Can I work with Date() and subtract days in a easy way?

I do not have a specific technical question, but I think sharing best way to proceed is a good thing, right?

I'm curious to know what are the best ways to proceed and open to any proposal!

Research `timestamp` and `datetime` column types. Also, google for `php Carbon` - it's a library using PHP's `DateTime`. End result is you having a few lines of code that perform exactly what you asked for. — N.B., Apr 08 '15 at 12:12
`DATETIME` should be used in MySQL and `new \DateTime()` in PHP. MySQL allows you to use `TIMESTAMPDIFF()` functions and PHP has `\DateInterval()`, combinations allow you everything one could need. — Daniel W., Apr 08 '15 at 12:13

cn007b · Accepted Answer · 2015-04-08T12:26:28.007

I recommend to store your date in mysql at field timestamp because you can use default value CURRENT_TIMESTAMP - it very helpful, and i think you shouldn't worry about it, there is a plenty of functions::

mysql:

select now();
+---------------------+
| now()               |
+---------------------+
| 2015-04-08 12:13:18 |
+---------------------+
select now() - interval 1 day;
+------------------------+
| now() - interval 1 day |
+------------------------+
| 2015-04-07 12:13:29    |
+------------------------+
select now() - interval 7 day;
+------------------------+
| now() - interval 7 day |
+------------------------+
| 2015-04-01 12:13:38    |
+------------------------+
select now() - interval 1 month;
+--------------------------+
| now() - interval 1 month |
+--------------------------+
| 2015-03-08 12:13:58      |
+--------------------------+

php:

<?php
var_export([
    date('Y-m-d H:i:s', strtotime('now')),
    date('Y-m-d H:i:s', strtotime('- 1 day')),
    date('Y-m-d H:i:s', strtotime('- 7 day')),
    date('Y-m-d H:i:s', strtotime('- 1 month')),
]);
/*
Result:

array (
  0 => '2015-04-08 15:15:42',
  1 => '2015-04-07 15:15:42',
  2 => '2015-04-01 15:15:42',
  3 => '2015-03-08 15:15:42',
)
*/

And sometimes very helpful to create table like:

CREATE TABLE t1 (
    ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

in result your field ts will be automatically seted and updated...

score 1 · Answer 2 · answered Apr 08 '15 at 12:14

1

You can store dates as DATETIME in your database.

Then in PHP convert it to manageable data using strtotime() and the date() functions

answered Apr 08 '15 at 12:14

paddyfields

1,466
2
15
25

score 0 · Answer 3 · answered Apr 08 '15 at 12:15

The best data type to work with is the DateTime class.

In order to perform substractions using the DateTime class, you'll need to use the DateInterval class.

You'll need some time to get on your ease using those two classes but it will make formatting or date operations easier afterwoods.

Best practice to manage date and days

3 Answers3