Recording AM/PM with DATETIME

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Recording AM/PM with DATETIME

Post 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?
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Recording AM/PM with DATETIME

Post by Bill H »

That depends on what you mean by "catch" the am/pm. You moght check the MySQL "HOUR()" function.
WebbieDave
Forum Contributor
Posts: 213
Joined: Sun Jul 15, 2007 7:07 am

Re: Recording AM/PM with DATETIME

Post by WebbieDave »

catch the? What exactly are you trying to do?
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Recording AM/PM with DATETIME

Post 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.
User avatar
EverLearning
Forum Contributor
Posts: 282
Joined: Sat Feb 23, 2008 3:49 am
Location: Niš, Serbia

Re: Recording AM/PM with DATETIME

Post 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));
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: Recording AM/PM with DATETIME

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Recording AM/PM with DATETIME

Post 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.
Post Reply