CODEWITHSUNDEEP
phpmysql
Codex73
Codex73

Reputation: 5766

Formatting Time 12 Hour Clock for MYSQL

How could a string be formatted depending an am/pm value so it could then be passed through strtotime and date?

I have a form with two inputs and one select element.

Need to insert to MySql type DATETIME which needs formatting to be current day + user selected time.

Possible String: '2:34:pm' which for insert formatted to '2012-03-22 14:34:00'

I read possible duplicate question, but I believe was difference scenario.

Upvotes: 2

Views: 2901

Answers (2)

benesch
benesch

Reputation: 5269

Step 1
Generate UNIX timestamp (number of seconds since January 1, 1970).

$time = mktime($_POST["hour"], $_POST["minute"], 0);

$_POST["hour"] should be in 24-hour format. You'll need to do basic input checking to ensure the values for $_POST["hour"] and $_POST["minute"] are valid.

To convert 12-hour to 24-hour time:

if ($_POST["ampm"] == "pm")
    $_POST["hour"] = ($_POST["hour"] % 12) + 12;

Step 2
Store to MySQL.

mysql_query("INSERT INTO table (date) VALUES (FROM_UNIXTIME($time))");

FROM_UNIXTIME(timestamp) will convert PHP's UNIX timestamp to a perfectly-formatted MYSQL date.

Presto!

Upvotes: 1

jnrbsn
jnrbsn

Reputation: 2533

Assuming your variables are named $hour, $minute, and $ampm:

$datetime = date('Y-m-d')
          . sprintf(
                ' %02d:%02d:00',
                ($ampm == 'pm' ? ($hour % 12) + 12 : $hour % 12),
                $minute
            );

Upvotes: 0

Related Questions