mysql2date() │ WP 0.71

Translates the specified date (as a string) into another format.

The format is specified identically to the PHP function date().

It is expected that $date is passed in local time (not UTC).

Formats 'U' and 'G' will return a number — the sum of the timestamp + the site's timezone offset. This is non-standard behavior! Historically, this arose when $date could be passed in UTC to get a Unix timestamp. See the example below.

Unlike the PHP date() function, this function will localize (translate) the month.

Works at an early stage of WordPress loading, even before the SHORTINIT constant.

Uses: wp_date()
Used By: get_post_modified_time(), get_comment_time(), get_comment_date(), get_the_date(), get_post_time()
1 time — 0.00018 sec (fast) | 50000 times — 4.42 sec (fast)

No Hooks.

Returns

String|Int|false.

  • number - when $format is U or G.
  • string - in other cases.
  • false - in case of an error.

Usage

mysql2date( $format, $date, $translate );
$format(string) (required)

The date format that we need to obtain. For example, writing j n Y will return: 25 12 2011. Format examples.

If the formats U or G are used, the $date parameter must specify the date in the GMT (UTC) timezone! See the example below.

$date(string) (required)
The date to be changed. It can be in any commonly accepted format. It is usually passed in MySQL format (Y-m-d H:i:s).
$translate(boolean)
true — the function will attempt to translate the names of months, days, etc., into the current language set in WordPress (localize).
Default: true

Examples

0

#1 How the function works 

echo mysql2date( 'd.M.Y H:i', '2015-07-24 15:23:14' ); // 24.Jul.2015 15:23

echo mysql2date( 'd-m-Y', '2020-10-25' );              // 25-10-2020

echo mysql2date( 'F j, Y', '2023-06-22 10:30:00' );    // June 22, 2023

echo mysql2date( 'l, F jS, Y \a\t g:i A', '2023-06-22 10:30:00', false ); // Thursday, June 22nd, 2023 at 10:30 AM
0

#2 Output the publication date of the post in the format d-m-Y: 

echo mysql2date( 'd-m-Y', $post->post_date ); // 02-12-2011

Examples of formats see here.

0

#3 The formats U and G have their own peculiarity!

Formats U and G work the same:

echo mysql2date( 'U', '2012-02-23 06:12:45' ); // 1329977565
echo mysql2date( 'G', '2012-02-23 06:12:45' ); // 1329977565

Important! When using the U and G formats, the provided date must also be in the UTC (GMT) zone!

For example, if you pass the post date $post->post_date, you will get a Unix timestamp + site timezone offset instead of the Unix timestamp of the date itself. This seems to be done for backward compatibility.

Example for verification:

// here we use the current time in the UTC (GMT) zone
$time = mysql2date( 'U', gmdate( 'Y-m-d H:i:s' ) );
var_dump( time() === $time ); // bool(true)

Due to such specifics, it is generally not recommended to use mysql2date() to get a UNIX timestamp because the behavior is not obvious.

How to correctly get a timestamp from a date (not in the UTC zone)

You can use the following code, which makes it clear what happens to the date:

$post_time = date_create( $post->post_date, wp_timezone() )->getTimestamp();

Here, we pass the post date in the site's timezone (not UTC) and get the UNIX timestamp (as expected, in the UTC timezone). This happens because in the second parameter of date_create(), we specify the timezone in which the date is provided.

OR like this:

date_create( $post->post_date_gmt, new \DateTimeZone('UTC') )->getTimestamp();

Changelog

Since 0.71 Introduced.

mysql2date() code WP 6.7.1

wp-includes/functions.php 
function mysql2date( $format, $date, $translate = true ) {
	if ( empty( $date ) ) {
		return false;
	}

	$timezone = wp_timezone();
	$datetime = date_create( $date, $timezone );

	if ( false === $datetime ) {
		return false;
	}

	// Returns a sum of timestamp with timezone offset. Ideally should never be used.
	if ( 'G' === $format || 'U' === $format ) {
		return $datetime->getTimestamp() + $datetime->getOffset();
	}

	if ( $translate ) {
		return wp_date( $format, $datetime->getTimestamp(), $timezone );
	}

	return $datetime->format( $format );
}

date time