[PHP/SQL] Single Timestamp

[Sidoh]

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).  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()
* date()
* My database class (obviously you can substitute your own here)

(Note the variables $month and $year have already been calculated/specified):

Code Select Expand


// 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.

[JTN Designer]

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.

[Sidoh]

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 )