Stats: get data by days

Databases
1

Hello,

I am working on a project where I will have to store stats (how many times a website is visited etc.). I will have to manipulate the data by day, month, year. As in: select the last 7 days / select last month etc. I was wondering if I should go with timestamp (INT) or datetime.

Regards,

-jj. :slight_smile:

2

i prefer datetime, because you can actually understand it when looking at the data, e.g. when using the dreaded, evil “select star” during preliminary testing

3

And here comes the trouble… :slight_smile:

It seems fairly easy to me to create unixdate with php using time(). I have no idea how to record a date in the datetime format. Is it done with php? Through a mysql query?

:slight_smile:

4

sorry, man, i don’t do php (and this ain’t the php forum, eh) :slight_smile:

but it’s definitely doable, all you have to do is use some php function to generate the date in this format: YYYY-MM-DD HH:MM:SS

5

@jjshell

Try this:


INSERT INTO sometable 
  (date_name, date_and_time)
VALUES
  ([FONT=verdana]'date_field_name', '2012-7-4 04:13:54'[/FONT]);

or this


 $sql = "
INSERT INTO sometable 
  (date_name, date_and_time)
VALUES
  ('date_field_name','" . NOW() . "')";

//Run your SQL query with PDO, MSQLi…


Steve
6

Thanks for your replies.

Just out of curiosity, and so I can make my mind, before I dive into this, how would a query grabbing the 7 days prior to today look like with datetime? Would it be a lot different with a timestamp?

:slight_smile:

7

compare…

using a timestamp column (integer) –


WHERE timestampcolumn >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)
  AND timestampcolumn  < UNIX_TIMESTAMP() -- defaults to today

using a datetime column –


WHERE datetimecolumn >= CURRENT_DATE - INTERVAL 7 DAY
  AND datetimecolumn  < CURRENT_DATE
8

@jjshell

While deciding you may also want to read this http://billauer.co.il/blog/2009/03/mysql-datetime-epoch-unix-time/ blog post as well as the comments to decide what date/time format you want to use.

Keep in mind that MySQL has advanced a lot since this article was written but the article does point out some other factors that might influence what you use.

Steve

9

the comments in that article make a more convincing case ~against~ using the unix timestamp integer

see also my point in post #2 above, which nobody has yet refuted

10

I read it the same way, hopefully jjshell sees it from both sides and has the same conclusion :slight_smile:

11

I’m sold! :slight_smile:

So, how should I enter a new datetime? Using NOW()?
Is datetime going to be harder to manoeuver than date? I’m thinking, why not store hours and seconds, I may need them in the future (but not right now).

12

use CURRENT_TIMESTAMP instead of NOW()

manoeuver? you mean, like with date and time functions?

and don’t bother storing hours and seconds – the minutes will feel so disrespected :smiley: :smiley: