working with time/date

[phppup]

I am setting up a form and plan to have the data stored in a table.  When I insert the information, I want to include the time and date of the submission.

Is there a best practice for apply so that pitfalls are avoided?

If I expect to want to review instances that occur from 9AM to NOON, or from one date to another, is it unwise to use colons, slashes, hyphens? 

[Barand]

Define the time submitted as

submitted DATETIME NOT NULL DEFAULT current_timestamp

That will automatically enter the time when you add a record. For example

mysql> create table test (name varchar(20), 
                          submitted DATETIME NOT NULL DEFAULT current_timestamp
                         );
Query OK, 0 rows affected (0.40 sec)

mysql> insert INTO test (name) VALUES ('Curly');
Query OK, 1 row affected (0.06 sec)

mysql> insert INTO test (name) VALUES ('Larry');
Query OK, 1 row affected (0.05 sec)

mysql> insert INTO test (name) VALUES ('Mo');
Query OK, 1 row affected (0.04 sec)

mysql> select * from test;
+-------+---------------------+
| name  | submitted           |
+-------+---------------------+
| Curly | 2022-05-06 21:18:40 |
| Larry | 2022-05-06 21:19:11 |
| Mo    | 2022-05-06 21:19:25 |
+-------+---------------------+
3 rows in set (0.00 sec)

 

[phppup]

@Barand Thanks, I have that.

But does MySql/PHP have a built in "understanding" when looking for submissions on May 5, 2022 between 9:05:00 AM and 1:30:00 PM?

Or should I just record time the time data as a 24 hour clock without colons and search WHERE time > 90500 && time < 133000 ? (As opposed to h:i:s)

[Barand]

Always store in the correct format (yyyy-mm-dd hh:ii:ss).

Retrieval is flexible...

mysql> SELECT name, submitted 
       FROM test 
       WHERE submitted BETWEEN '2022-05-05 09:05:00' AND '2022-05-05 13:30:00';
+-------+---------------------+
| name  | submitted           |
+-------+---------------------+
| Curly | 2022-05-05 10:46:30 |
| Larry | 2022-05-05 12:20:30 |
+-------+---------------------+

mysql> SELECT name, submitted 
       FROM test 
       WHERE submitted BETWEEN '20220505090500' AND '20220505133000';
+-------+---------------------+
| name  | submitted           |
+-------+---------------------+
| Curly | 2022-05-05 10:46:30 |
| Larry | 2022-05-05 12:20:30 |
+-------+---------------------+

mysql> SELECT name, submitted 
       FROM test 
       WHERE DATE(submitted) = '2022-05-05' AND TIME(submitted) BETWEEN '09:05:00' AND '13:30:00';
+-------+---------------------+
| name  | submitted           |
+-------+---------------------+
| Curly | 2022-05-05 10:46:30 |
| Larry | 2022-05-05 12:20:30 |
+-------+---------------------+

mysql> SELECT name, submitted 
       FROM test 
       WHERE DATE(submitted) = '20220505' AND TIME(submitted) BETWEEN '090500' AND '133000';
+-------+---------------------+
| name  | submitted           |
+-------+---------------------+
| Curly | 2022-05-05 10:46:30 |
| Larry | 2022-05-05 12:20:30 |
+-------+---------------------+