So I'm revamping the entire core engine to my website CMS and I decided to use a single timestamp (UNIX epoch) instead of using the DATE and TIME datatypes that MySQL has. It seems a lot less bulky and there is more data contained in a single timestamp than two bulky columns for DATE and TIME.
I ran into a problem with this when I was coding the archived news section of my website (Now viewable here (http://www.sidoh.org/test/scms/index.php/news/archived)). There was no real integrated way to select posts added in a specific month, so I had to think of some method.
Here's what I came up with:
I'd find the timestamp for the first day in the month (at 00:00:00) and the timestamp for the last day in the month (at 23:59:59) and all posts that had a timestamp that fell inbetween these two would have been posted within the month. Using PHP, I came up with a pretty good solution.
Toolbox:
* strtotime() (http://us2.php.net/strtotime)
* date() (http://us2.php.net/manual/en/function.date.php)
* My database class (obviously you can substitute your own here)
(Note the variables $month and $year have already been calculated/specified):
// Get the UNIX timestamp for the first day in this month
$f_stamp = date('U', strtotime("$year-$month-1 00:00:00"));
// Get the UNIX timestamp for the last day in this month
$l_stamp = date('U', strtotime(date('F t, Y 23:59:59', strtotime("$year-$month-1"))));;
$db->sql_query("SELECT * FROM sidoh_news WHERE
`news_timestamp` >= $f_stamp AND
`news_timestamp` <= $l_stamp;");
As you can see by the link I provided, it works pretty well! :)
Just thought I'd share this with you all as I found it pretty useful.
I think I may understand what you're asking. Why not just insert a value defined such as:
define("TIME", date("g:i:sa"));
Then obviously in your INSERT query just insert TIME.
Like I said what I think you want, other then that just format UNIX_TIMESTAMP() to your liking.
Quote from: JTN Designer on November 30, 2005, 11:29:21 AM
I think I may understand what you're asking. Why not just insert a value defined such as:
define("TIME", date("g:i:sa"));
Then obviously in your INSERT query just insert TIME.
Like I said what I think you want, other then that just format UNIX_TIMESTAMP() to your liking.
No, I already solved this problem. I was posting my solution because I thought others might find it useful. :P
Also, why would you define a constant when you could just define a variable? Better yet, why not just break the string and run the function while constructing the query? If I wanted two columns (or even one column not using the UNIX epoch timestamp), I could just use MySQL functionality. IE use the TIME, DATE or TIMESTAMP datatypes for columns and insert rows using the NOW() function.
http://www.sidoh.org/test/scms/index.php/news/archived ( Works fine )