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?
Recording AM/PM with DATETIME
Moderator: General Moderators
- 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
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
catch the? What exactly are you trying to do?
Re: Recording AM/PM with DATETIME
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.
I know how to get the AM/PM, I am wondering what the most efficient way of recording it is, without using UNIX timestamp.
- EverLearning
- Forum Contributor
- Posts: 282
- Joined: Sat Feb 23, 2008 3:49 am
- Location: Niš, Serbia
Re: Recording AM/PM with DATETIME
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 in your query,
OR
use PHP functions date or strftime with strtotime
Code: Select all
DATE_FORMAT(`date`,'%p')OR
use PHP functions date or strftime with strtotime
Code: Select all
strftime('%p', strtotime($date));
date('A', strtotime($date));- 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
Code: Select all
$Query = "SELECT * FROM Table WHERE HOUR(Date)>11"Code: Select all
$Query = "SELECT * FROM Table WHERE HOUR(Date)<12"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
Code: Select all
SELECT DATE_FORMAT(field_name, '%p') AS meridian FROM TABLE;
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';