Rookie problem with date format

cave monkey · #1 16-Mar-2004, 13:51

Help please. I am very new to PHP and mysql, and am trying to write a simple script to display tide tables for my area. My problem is this: My tides data in the database uses the default mysql time format (ex. 22:17:32) and I would like the output of my query to be displayed as 10:17 PM (with no seconds). My query is executed as "Select * from `tides` where `date` = NOW()" and works fine except for the time format. I have played with DATE_FORMAT() as described on the MySQL homepage and in the article on this site, but can't get the query to execute as I assume it has to mach the data in the database. Maybe DATE_FORMAT() would work when querying for a specifc date, but not sure how to implement it with SELECT * and the variable NOW(). Should I be affecting the output via PHP date formats or MySQL date formats? Thanks for the help and the patience. DM

JdS · #2 17-Mar-2004, 05:11

Hello cave monkey,

Basically, you can use DATE_FORMAT() for this but just in case I've misunderstood your question, you should also try using TIME_FORMAT().

Here's an example:

PHP Code:


<?php
// suppose the column you want to format is in the TIME column type
// so, a sample formatting could look like this:
$sql  =  'SELECT TIME_FORMAT(`date`, \'%h:%I%p"\') AS `tide_time`
         WHERE `date` = CURTIME()';

?>

It goes without saying that there MUST be a record that matches the time EXACTLY, down to the seconds, for it to return a result. If you want it to match a range of time, then just reply with the details.

cave monkey · #3 17-Mar-2004, 11:31

Thanks for your response. I found a similar solution through trial and error before reading your post. My final query used TIME_FORMAT() as you recommend....

PHP Code:


$query = "SELECT TIME_FORMAT(`time`,'%h:%i %p')AS `time`,`tide`,`type`,`units` FROM `main` WHERE `date` = NOW()  LIMIT 0, 30";        
$result = mysql_query($query);

A couple of questions for you, if you have the time. Can DATE_FORMAT() be used when inputting data through a web interface, so that an entry of 03/14/04 is accepted by MySQL as 2004-03-14? Also, is it okay to use multiple AS statements in a query such as mine? Thanks for your time. DM

JdS · #4 18-Mar-2004, 09:36

Yes, it's possible but I don't think that's such a good idea.

It's also normal to use multiple AS (or aliases) in your queries; although I don't see it in your example above.

cave monkey · #5 18-Mar-2004, 09:41

Thanks again for the help. It's appreciated.

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
problem with php5 cgi installation	fab13	Apache Web Server Forum	3	19-Nov-2003 10:11
Time Problem in Program	MrSmiley	C Programming Language	1	03-Nov-2003 13:00
unwanted scrollbar problem	kelly001	Web Design Forum	3	24-Oct-2003 11:44

Rookie problem with date format

Thanks!