mySQL day/time Best Practices

[StevenOliver]

I want to be able to record the date and time  of page visits. And then later, view them grouped by day or month.

What is the "best practices" way to do this? (Googling this, I find two philosophies:1.)  letting mySQL create the time stamp, and 2.) letting PHP create the time stamp, and inserting this value into mySQL.)

Currently, page displays "November 22, 2018 at 6:19am" to visitor, and then inserts this value into mySQL table using this code:

$date = new DateTime();
$currentTime = $date->sub(new DateInterval('PT7H'))->format('F j Y \a\t g:ia');
echo $currentTime;
// also, insert $currentTime into mySQL table....

But server is finicky!I have to adjust DateInterval('PT7H') every few days (off by one or more hours), and, this code doesn't lend itself to "selecting by day/month/year" in mySQL.

Thank you.

Edited November 22, 2018 by StevenOliver

[ginerjm]

Not sure what your logic is trying to accomplish but if it were me I would be saving the value in $date to the table and nothing else.  When you retrieve the date value (in MySQL that column would be defined as 'datetime') you can format it for display in any way you want.  As it is, you are storing a string value and that is just not right.

[Barand]

Store as DATETIME or TIMESTAMP (the two are pretty much the same). Both can be given a default value of CURRENT_TIMESTAMP which will automatically record the time of record insert. Their formats are both "Y-m-d H:i:s" (plus optional microseconds)

Using those types also gives you the ability to use the many datetime functions within mysql (unlike varchar() ) plus they can be compared for <, > etc and therefore sortable (which your format is not)

EG MONTH(mydate) returns the month number, DATE(mydate) returns just the date portion (see mysql datetime functions)

To get your date display format you can use either

• PHP : date ('F d, Y g:i a', strtotime($row['mydate']), or
• mysql: SELECT DATE_FORMAT(mydate, ' %M %e, %Y at %l:%i%p' ) as mydatetime

Edited November 22, 2018 by Barand

[StevenOliver]

10 minutes ago, ginerjm said:

Not sure what your logic is trying to accomplish but if it were me I would be saving the value in $date to the table and nothing else.  When you retrieve the date value (in MySQL that column would be defined as 'datetime') you can format it for display in any way you want.  As it is, you are storing a string value and that is just not right.

Thank you for replying -- I'll go ahead and change my column definition. The way I currently have it is really devoid of logic ? I like your way better than mine!

Edited November 22, 2018 by StevenOliver

[StevenOliver]

7 minutes ago, Barand said:

Store as DATETIME or TIMESTAMP (the two are pretty much the same).

Thank you for your reply. I just now looked up "DATETIME vs TIMESTAMP" and found that mySQL's native "DATETIME" is faster and lighter, but TIMESTAMP is better when dealing with time zones.

I wonder if TIMESTAMP would indeed be better because of the finicky server (I think my webhost uses several servers located in different time-zones which makes me have to readjust DateInterval('PT7H') every few days)?

 

 

[ginerjm]

For php you can make a setting in the main php.ini file that selects your preferred timezone.  Do a phpinfo() call and see what the current default setting is.  If you don't like it, have your hosting co. place the correcting setting in the .ini file.  If they say they can't do that, make them place a copy of the main php.ini file in your domain area and make the setting change for you.

Edited November 22, 2018 by ginerjm

[StevenOliver]

Thank you, I shall do that!!