jjshell — 2012-01-27T11:51:33-05:00 — #1
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.
r937 — 2012-01-27T12:04:11-05:00 — #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
jjshell — 2012-01-27T13:49:21-05:00 — #3
And here comes the trouble...
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?
r937 — 2012-01-27T14:17:55-05:00 — #4
sorry, man, i don't do php (and this ain't the php forum, eh)
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
serverstorm — 2012-01-27T14:30:45-05:00 — #5
INSERT INTO sometable
(<font face='verdana'>'date_field_name', '2012-7-4 04:13:54'</font>);
$sql = "
INSERT INTO sometable
('date_field_name','" . NOW() . "')";
//Run your SQL query with PDO, MSQLi...
jjshell — 2012-01-27T15:10:09-05:00 — #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?
r937 — 2012-01-27T15:16:54-05:00 — #7
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
serverstorm — 2012-01-27T16:04:12-05:00 — #8
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.
r937 — 2012-01-27T16:34:08-05:00 — #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
serverstorm — 2012-01-27T17:37:02-05:00 — #10
I read it the same way, hopefully jjshell sees it from both sides and has the same conclusion
jjshell — 2012-01-28T14:27:53-05:00 — #11
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).
r937 — 2012-01-28T15:13:13-05:00 — #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