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.
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
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?
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
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...
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?
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
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.
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
I read it the same way, hopefully jjshell sees it from both sides and has the same conclusion
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).
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
This topic is now archived. It is frozen and cannot be changed in any way.