Date Time Column Type in Mysql (AM or PM syntax?)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Date Time Column Type in Mysql (AM or PM syntax?)

Post by kendall »

Hello,

If i had a table with a column type DATETIME

and i had any entry 2003 06 06 06:30:15 does can mysql determine if its an am or pm?

What is needed to implement wheather is a day or night datetime entry?

Kendall
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post by delorian »

DATETIME has 24 hours TIME so 6:30:15 is a AM.
corlando
Forum Newbie
Posts: 21
Joined: Sun Jun 15, 2003 10:07 pm

Post by corlando »

MySQL Date Functions
http://www.mysql.com/doc/en/Date_and_ti ... tions.html

AM

Code: Select all

... WHERE HOUR(DateTimeColumn) < 12
PM

Code: Select all

... WHERE HOUR(DateTimeColumn) >= 12
User avatar
releasedj
Forum Contributor
Posts: 105
Joined: Tue Jun 17, 2003 6:35 am

Post by releasedj »

Or Just:

Code: Select all

SELECT DateTimeColumn, DATE_FORMAT(DateTimeColumn, '%p') as ampm FROM table;
ampm will now either be AM or PM.
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Date Time Column Type in Mysql (AM or PM syntax?)

Post by kendall »

Ok,

Well why i asked this is that i did a test post in the PM and when viewing the message the time was stamped with an AM. Now my DATETIME column is set to its default. and i retrieve the date which is formatted in mysql command

[syntax=php]SELECT *,DATE_FORMAT(Date, '%D %a %M %Y %r') AS TheDate[/syntax]

Now assuming that the deafault is 24 hour how does it affect my date translation bewteen php and mysql of input and ouput ?

Kendall
User avatar
releasedj
Forum Contributor
Posts: 105
Joined: Tue Jun 17, 2003 6:35 am

Post by releasedj »

Do you mean that the timestamp on the server did not record the correct time?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Date Time Column Type in Mysql (AM or PM syntax?)

Post by kendall »

realeasedj,

no... what i mean is that if there was an entry 2003 06 06 6:30:45 how does mysql no if it is 6:30 am or 6:30 pm? Mysql manual said that the default entry for dates is year-month-date hh:mm:ss but they dont affix an am or pm can i manually insert it using php?

Kendall
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

MySQL takes dates in 24 hour format, 6:30:45 would be AM, if you wanted it to be PM you would have to enter 18:30:45. (Which is pretty much what delorian said straight off).

Mac
Post Reply