将 AM PM 时间字符串存储到 MySQL 中的 TIME 数据类型中，并在显示时使用 AM PM 检索?

store AM PM time string into TIME datatype in MySQL and retrieve with AM PM while display?(将 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.

这篇关于将 AM PM 时间字符串存储到 MySQL 中的 TIME 数据类型中，并在显示时使用 AM PM 检索?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！