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