Page 1 of 1

Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 9:18 am
by icesolid
I like using the DATETIME and CURRENTTIMESTAMP in MySQL when recording a date. The only problem is I would like to catch the AM/PM in the most efficeint manor possible. Is the only way of doing so making a field ex: am_pm and recording it from a date("A"); function every time?

Any suggestions?

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 10:35 am
by Bill H
That depends on what you mean by "catch" the am/pm. You moght check the MySQL "HOUR()" function.

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 10:41 am
by WebbieDave
catch the? What exactly are you trying to do?

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 10:43 am
by icesolid
How does that answer my storing the AM/PM in my database when using DATETIME timestamp?

I know how to get the AM/PM, I am wondering what the most efficient way of recording it is, without using UNIX timestamp.

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 11:57 am
by EverLearning
MySql DATETIME records the time in 24-hour format. So no AM/PM. If you later want to show date in AM/PM format you can use MySql DATE_FORMAT

Code: Select all

DATE_FORMAT(`date`,'%p')
in your query,

OR

use PHP functions date or strftime with strtotime

Code: Select all

strftime('%p', strtotime($date));
date('A', strtotime($date));

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 2:53 pm
by Bill H

Code: Select all

$Query = "SELECT * FROM Table WHERE HOUR(Date)>11"
Will give you records with times 12:00 and later. Those are pm times.

Code: Select all

$Query = "SELECT * FROM Table WHERE HOUR(Date)<12"
Will give you records with times 11:59 and earlier. Those are am times.

That's how my post answered what I assumed to be your question, which was very vague.
If you didn't like the answer ask the question in a more specific manner.

Re: Recording AM/PM with DATETIME

Posted: Thu Jul 10, 2008 3:19 pm
by Benjamin

Code: Select all

 
SELECT DATE_FORMAT(field_name, '%p') AS meridian FROM TABLE;
 
It's already captured in the timestamp field. What exactly are you trying to do? Pull records based on it?

Code: Select all

 
SELECT * FROM table_name WHERE DATE_FORMAT(field_name, '%p') = 'AM';
SELECT * FROM table_name WHERE DATE_FORMAT(field_name, '%p') = 'PM';
 
There's a number of ways to get this data.