How to deal with Date and Time in WordPress

If you want to shake the developer’s faith, ask them to do something with the dates. It’s always challenging. In WordPress, it’s in twice tricky.

WordPress has many settings related to date and time. These settings allow site owners easily modify timezone, date and time formats, and the first day of the week.

Further, I will explain how to deal with these settings in different cases.

Translations

Some date formats include months or days names that should be translatable. For instance, the F format should return January for the English website and enero for the Spain one.

It would be nice if you use the wp_date or date_i18n WordPress functions to keep dates translatable.

Difference between these functions: the date_i18n is the legacy function with backward compatibility for old code. Honestly, I don’t see any practical reason to use the date_i18n function nowadays, and you can use the wp_date everywhere. However, you must carefully test your changes when replacing the date_i18n with wp_date because it can take some side effects.

Format

Since WordPress has settings for date and time formats, in most cases, we have to get them from the options:

$date_format      = get_option( 'date_format' );
$time_format      = get_option( 'time_format' );
$date_time_format = sprintf( '%s %s', get_option( 'date_format' ), get_option( 'time_format' ) );

Time Zones

Time zones and time shifting may be the most complicated part when dealing with dates.

WordPress stores the time zones in the number of hours that needs to be shifted from UTC+0. For instance, India has UTC+05:30, and in the database, it’s 5.5, but California has UTC-08:00, and in the database is -8.

So, the gmt_offset option can be positive or negative, integer or fractional number. Despite everything sounding very scary, it is actually very convenient to use in code.

For example, let’s find the current date and time despite the website settings:

$offset = (float) get_option( 'gmt_offset' ) * HOUR_IN_SECONDS;

Custom Function

To sum up, we can create our own function that allows you don’t remember all of these options’ names and just always use it in your codebase:

function wppunk_datetime_format( $timestamp, $format = '', $is_utc = false ) {

	if ( $format === '' ) {
		$format = sprintf( '%s %s', get_option( 'date_format' ), get_option( 'time_format' ) );
	}

	$timezone = $is_utc ? 'UTC' : '';

	return wp_date( $format, $timestamp, timezone );
}

First Day of the Week

If you have logic in your code that relates to data for a specific week, you need to consider that WordPress has the start_of_week option. For example, the number of sent messages in the past week. When did last week start? On Sunday? On Monday? Who knows?

Generally, the option returns a number between 0 and 6, where 0 is Sunday, and 6 is Saturday.

$start_of_week = (int) get_option( 'start_of_week', 0 );

Storing Date and Time in the Database

Never store the date and time in WordPress format, as well as with offsets. It just doesn’t make sense. The data becomes outdated whenever you change WordPress’s date and time-related settings.

In a database, you only have one correct option is storing date and time in SQL format – YYYY-MM-DD HH:MI:SS, e.g. 2023-01-28 14:39:12.

Firstly, it’s human-readable, and those who watched your plain data in SQL can understand where it was submitted without extra steps. Secondly, SQL works pretty well with its date format, and you can make any date changes directly in SQL queries.

Date and Time SQL Queries

In most cases, when you need to get some date period, you can use WordPress functions or objects e.g. WP_Query, get_posts, etc. However, you must write your own SQL queries when dealing with custom tables. And sometimes, these queries are depended on dates.

For instance, I need to get the entries that were made on January 28th. For UTC+0, the query is:

SELECT * FROM records WHERE date >= 28.01.2023 00:00:00 AND date < 29.01.2023 00:00:00

The query is super simple, but what if a site owner lives in California (UTC-08:00)?

The day in California starts 27.01.2023 at 16:00:00 and ends 28.01.2023 at 16:00:00. You have to calculate the offset on the PHP side:

$offset          = (float) get_option( 'gmt_offset' ) * HOUR_IN_SECONDS;
$timestamp_start = date_format( date_create( '2023-01-28' ), 'U' );
$timestamp_end   = date_format( date_create( '2023-01-29' ), 'U' );

$wpdb->query(
	$wpdb->prepare(
		'SELECT * FROM records WHERE date >= %s AND date < %s',
		date_format( $timestamp_start + $offset, 'Y-m-d H:i:s' ),
		date_format( $timestamp_end + $offset, 'Y-m-d H:i:s' )
	)
);

Or you can calculate it on the SQL side. Fortunately, SQL has a lot of instruments that help you work with dates:

$wpdb->query(
	$wpdb->prepare(
		'SELECT * FROM records WHERE date >= DATE_ADD(%1$s, INTERVAL %3$d MINUTE) AND date < DATE_ADD(%2$s, INTERVAL %3$d MINUTE)',
		date_format( date_create( '2023-01-28' ), 'Y-m-d H:i:s' ),
		date_format( date_create( '2023-01-29' ), 'Y-m-d H:i:s' ),
		get_option( 'gmt_offset' ) * HOUR_IN_SECONDS
	)
);

Next point, don’t use the NOW() function in SQL. SQL servers have their own timezone and offsets. To get the current time, you can use the UTC_TIMESTAMP function instead.

I hope next time it will be easier for you to deal with date and time in WordPress after reading the article. Anyway, if you have any questions, you can ask them in the comments or on Twitter.