将 AM PM 时间字符串存储到 MySQL 中的 TIME 数据类型中,并在显示时使用 AM PM 检索?
我在前端输入日期为 10:00 AM、12:00 PM 等...(表示 12 小时格式).现在我想将该值保存在 time 数据类型列中的数据库中.如何将该 AM PM 值保存到 MySQL 中的 time 数据类型中,并再次希望在前端显示附加 AM PM 的时间?
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' );
我将继续假设您使用 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 );
这将返回一个结果集,其中格式化数据位于 *_formatted
字段中
This will return a result set where the formatted data is in the *_formatted
fields
将 %m
(月)调整为 %i
(分钟).感谢 Donny 发现的失误.
Adjusted %m
(month) to %i
(minutes). A thank you to Donny for the well spotted slip up.
相关文章