商店上午下午的时间串入TIME数据类型在MySQL和检索与AM PM同时显示? [英] store AM PM time string into TIME datatype in MySQL and retrieve with AM PM while display?
本文介绍了商店上午下午的时间串入TIME数据类型在MySQL和检索与AM PM同时显示?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我M在前端输入日期 10:00 下午12:00 等。(指12小时格式)。
现在我要保存在数据库中的时间数据类型列的值。我如何保存上午下午值转换成时间数据类型在MySQL和再次要显示的时间追加上午下午在前端
I m entering date in front end as 10:00 AM , 12:00 PM etc...( means 12 Hours format). now I want to save that value in database in time datatype column. How do I save that AM PM value into time datatype in MySQL and again want to display time appending AM PM on front end?
推荐答案
要插入:
# replace first argument of STR_TO_DATE with value from PHP/frontend
TIME( STR_TO_DATE( '10:00 PM', '%h:%i %p' ) );
要选择:
# replace first argument with your time field
TIME_FORMAT( '22:00:00', '%h:%i %p' );
编辑:结果
我就继续和presume你用mysql的lib功能。
I'll just go ahead and presume you use mysql lib functions.
// first sanitize the $_POST input
// also, make sure you use quotes to identify the $_POST keys
$open = mysql_real_escape_string( $_POST[ 'MondayOpen' ] );
$close = mysql_real_escape_string( $_POST[ 'MondayClose' ] );
// this is the query, which should work just fine.
$sql = '
INSERT INTO
`table_lib_hours`
SET
`day_name` = "Monday",
`day_open_time` = TIME( STR_TO_DATE( "' . $open . '", "%h:%i %p" ) ),
`day_close_time` = TIME( STR_TO_DATE( "' . $close . '", "%h:%i %p" ) )
';
$result = mysql_query( $sql );
然后检索值:
$sql = '
SELECT
`day_open_time`,
`day_close_time`,
TIME_FORMAT( `day_open_time`, "%h:%i %p" ) as day_open_time_formatted,
TIME_FORMAT( `day_close_time`, "%h:%i %p" ) as day_close_time_formatted
FROM
`table_lib_hours`
WHERE
`day_name` = "Monday"
';
$resultset = mysql_query( $sql );
其中,格式化的数据是这将返回结果集的 * _格式化
字段
编辑:结果
调整%M
(月)为%I
(分钟)。感谢你给唐尼的井发现滑倒摔伤。
Adjusted %m
(month) to %i
(minutes). A thank you to Donny for the well spotted slip up.
这篇关于商店上午下午的时间串入TIME数据类型在MySQL和检索与AM PM同时显示?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文