Jump to content
StevenOliver

mySQL day/time Best Practices

Recommended Posts

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 by StevenOliver

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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 by Barand

Share this post


Link to post
Share on other sites
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 by StevenOliver

Share this post


Link to post
Share on other sites
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)?

 

 

Share this post


Link to post
Share on other sites

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 by ginerjm

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.