CODEWITHSUNDEEP
phpmysql
the_nuts
the_nuts

Reputation: 6074

Storing date and hour (without minutes and seconds) in MySQL

I need to store dates with hours, but without minutes or seconds. That's because I want the data to be grouped by hour, and be able to insert with ON DUPLICATE KEY UPDATE... if two events happen in the same hour.

I've seen some applications using a DATETIME field, and inserting with

date("Y-m-d H") . ':00:00'

is this the best practice or are there better solutions?

Upvotes: 1

Views: 2793

Answers (4)

Dilraj Singh
Dilraj Singh

Reputation: 1011

From my best of know it will better to store date in integer format like epoch time because 1. it will store every thing like date month year hours minutes seconds. 2. While searching using epoch time it will faster because integer comparison is faster than string comparison.

Use of epochtime in MySQL

Upvotes: 0

Will B.
Will B.

Reputation: 18426

It really depends on your use case.

  • If you need to ever query a specific date or specific hour entry. You should use separate DATE and TIME (or TINYINT) column types, with a UNIQUE INDEX on both columns and not a DATETIME column type.
  • If you only ever need the date with the hour a DATETIME column with a UNIQUE INDEX would be suitable.

I prefer to ensure the resulting data is correct by accepting the full date as a column. This makes it easier for querying on for reporting purposes, etc, as well as for standardizing the other column values.

mysql> CREATE TABLE `test` (
    -> `my_date` DATE NULL DEFAULT NULL,
    -> `my_hour` TIME NULL DEFAULT NULL,
    -> `full_date` DATETIME NULL DEFAULT NULL,
    -> `duplicates` INT(11) NULL DEFAULT '0',
    -> UNIQUE INDEX `my_date_my_hour` (`my_date`, `my_hour`)
    -> );
Query OK, 0 rows affected (0.18 sec)

Then instead of formatting the insert value in PHP or declaring the individual column values, format your query's input data, using the full_date column as a reference.

mysql> INSERT INTO test (full_date, my_date, my_hour) 
    -> VALUES('2016-04-01 20:30:22', DATE(full_date), TIME_FORMAT(full_date, '%H:00:00'))
    -> ON DUPLICATE KEY UPDATE
    -> duplicates = duplicates+1;
Query OK, 1 row affected (0.04 sec)

If you use TINYINT for my_hour column, change the insert value to TIME_FORMAT(full_date, '%H').

mysql> SELECT * FROM test;
+------------+----------+---------------------+------------+
| my_date    | my_hour  | full_date           | duplicates |
+------------+----------+---------------------+------------+
| 2016-04-01 | 20:00:00 | 2016-04-01 20:30:22 |          0 |
+------------+----------+---------------------+------------+
1 row in set (0.00 sec)

Insert another entry changing the Minute and Second, to cause duplicates to be incremented.

mysql> INSERT INTO test (full_date, my_date, my_hour) 
    -> VALUES('2016-04-01 20:32:20', DATE(full_date), TIME_FORMAT(full_date, '%H:00:00'))
    -> ON DUPLICATE KEY UPDATE
    -> duplicates = duplicates+1;
Query OK, 2 rows affected (0.04 sec)

mysql> SELECT * FROM test;
+------------+----------+---------------------+------------+
| my_date    | my_hour  | full_date           | duplicates |
+------------+----------+---------------------+------------+
| 2016-04-01 | 20:00:00 | 2016-04-01 20:30:22 |          1 |
+------------+----------+---------------------+------------+
1 row in set (0.00 sec)

Then you can concatenate the my_date and my_hour columns into a single column value to accept in PHP.

mysql> SELECT *, CONCAT(my_date, 'T', my_hour) as date_hour FROM test;
+------------+----------+---------------------+------------+---------------------+
| my_date    | my_hour  | full_date           | duplicates | date_hour           |
+------------+----------+---------------------+------------+---------------------+
| 2016-04-01 | 20:00:00 | 2016-04-01 20:30:22 |          1 | 2016-04-01T20:00:00 |
+------------+----------+---------------------+------------+---------------------+
1 row in set (0.00 sec)

Then in PHP you can format the resulting recordsets.

$date = new DateTime($row['date_hour']);
$timestamp = strtotime($row['date_hour']);
//or
$date = new DateTime($row['my_date'] . 'T' . $row['my_hour']);
$timestamp = strtotime($row['my_date'] . 'T' . $row['my_hour']);

Upvotes: 1

tadman
tadman

Reputation: 211740

If you want to group things by hour and you want it to be performant create a derivative column that stores the time value in hours since epoch. Normally that's as easy as dividing the epoch time in seconds by 3600.

You'll have a schema like this:

event_time DATETIME,
event_hour INT

Then add values like this:

INSERT INTO ... (..., event_time, event_hour)
  VALUES (..., '2016-05-30 13:00:00', 406841)

You can set a UNIQUE constraint on the event_hour column.

Upvotes: 0

nospor
nospor

Reputation: 4220

Yes, this is good practice. Date/Times should be keep in date/time fields - thanks to that you can still use special date and time functions which is very helpful.

Upvotes: 1

Related Questions