7 Replies - 11436 Views - Last Post: 03 February 2009 - 09:41 AM Rate Topic: -----

#1 Xmod  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 61
  • Joined: 19-January 07

PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 03:46 PM

hello guys im doing a blog in which in one of the pages i want to display posts from a date example: 02-02-2009 the only problem I have is that my post table in the database im storing the date in unix time as an integer to easily manipulated in the php script... well im wondering if anyone knows a function that would make the timestamp in the sql call look like date

EXAMPLE:

$date = 02-02-09;

SELECT * FROM post WHERE time_stamp = $date; how can i make time stamp look like 02-02-09 to compare it with a date

Is This A Good Question/Topic? 0
  • +

Replies To: PHP - MYSQL COMPARING TIMESTAMP TO DATE

#2 pr4y  Icon User is offline

  • Location: 127.0.0.1
  • member icon

Reputation: 35
  • View blog
  • Posts: 621
  • Joined: 19-September 08

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 04:02 PM

Unix/Linux timestamps are the number of seconds since midnight, January 1, 1970 GMT (referred to as the Epoch). They are much easier to programmatically manipulate than date/time strings. For example, if you need to refer to 10 days from today, it is easier to add (10 days * 24 hours * 60 minutes * 60 seconds) seconds to the current timestamp than it is to mess with MM/DD/YY format.


Hope that gives you the information you are looking for. I'm not going to write the algorithm FOR you, but this does have all the information you need to get it done.


Let me know if you have any syntax questions!
Was This Post Helpful? 0
  • +
  • -

#3 mocker  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 50
  • View blog
  • Posts: 466
  • Joined: 14-October 07

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 04:09 PM

Use the mysql STR_TO_DATE function to take a string like '02-02-09' and turn it into a properly formatted date
http://dev.mysql.com...ion_str-to-date
mysql> select str_to_date('02-02-09','%m-%d-%y');
+------------------------------------+
| str_to_date('02-02-09','%m-%d-%y') |
+------------------------------------+
| 2009-02-02						 | 
+------------------------------------+
1 row in set (0.00 sec)



to change a date into a unix timestamp use UNIX_TIMESTAMP
mysql> select unix_timestamp(str_to_date('02-02-09','%m-%d-%y'));
+----------------------------------------------------+
| unix_timestamp(str_to_date('02-02-09','%m-%d-%y')) |
+----------------------------------------------------+
|										 1233532800 | 
+----------------------------------------------------+
1 row in set (0.00 sec)


Was This Post Helpful? 0
  • +
  • -

#4 pr4y  Icon User is offline

  • Location: 127.0.0.1
  • member icon

Reputation: 35
  • View blog
  • Posts: 621
  • Joined: 19-September 08

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 04:15 PM

Good suggestion mocker, I wasn't sure if he wanted the actual PHP code to manipulate the date/time string, or if he wanted as an option to just use a MySQL query.

Either way, looks like we got it covered :P
Was This Post Helpful? 0
  • +
  • -

#5 Xmod  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 61
  • Joined: 19-January 07

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 04:20 PM

i will look into it thanks

This post has been edited by Xmod: 02 February 2009 - 04:21 PM

Was This Post Helpful? 0
  • +
  • -

#6 Xmod  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 61
  • Joined: 19-January 07

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 04:26 PM

well what i wanted to know if there is a way to do something like this

$date = date("Y-m-d");
$query = "SELECT * FROM post WHERE date("Y-m-d", timestamp) = $date;
Was This Post Helpful? 0
  • +
  • -

#7 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 02 February 2009 - 08:34 PM

You want the MySQL functions UNIX_TIMESTAMP() and FROM_UNIXTIME(), which convert dates to UNIX timestamps and UNIX timestamps to dates, respectively.

Using those, you could write something like this:
SELECT * FROM post WHERE FROM_UNIXTIME(timestamp) = '2009-02-02';

Was This Post Helpful? 1
  • +
  • -

#8 kiwi2  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 8
  • View blog
  • Posts: 178
  • Joined: 16-September 08

Re: PHP - MYSQL COMPARING TIMESTAMP TO DATE

Posted 03 February 2009 - 09:41 AM

to be honest I'm a hack somtimes when it comes to mysql queries, I run the query and organize my date using standard php functions like mktime(), and getdate(), I find it easier, but then diffrent strokes, what can I say!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1