[Resolved] Universal time for Toolset date field in WordPress admin

[Author]

Posts

[Pat]

I want to retrieve the french time (Paris). So, yes, there is a 2 hours delta with UTC. Currently, if I place 9h in the field wpcf-date-debut-inscription-site, then the output condition runs at 11h
Regards
Pat

[Nigel]

Hi Pat

Minesh is on vacation, so let me jump back in here.

I can help you modify your code, but I want to clarify something first (you already alluded to it yourself).

If you added the dates recently then they will be based on UTC+2 (Paris, summertime). If in November you modify the dates, they would then be based on UTC+1 (Paris, wintertime).

The problem is when retrieving those dates to compare with the current date knowing whether they were entered during UTC+2 or UTC+1 (and we may be retrieving them during either UTC+2 or UTC+1).

That's why it is better to store them in a standard way in the database in the first place.

I'm just going through this on a local test site to verify exactly how it works—whether it is based on server timezone settings or site timezone settings, for example—and when I have it working I'll get back to you.

[Pat]

Hi Nigel,

If I understand correctly, if I modify the dates in the WP admin, then, the UCT time will be stored in the database. If I do this thanks to a form, then the local time will be stored.
Is that right and if yes, how the time change is managed by Toolset (is there something special added to the field in order to manage it)?

Thanks for your support
Pat

[Nigel]

Hi Pat

Sorry for the delay getting back to you, we have had problems here on toolset.com and the forums (hopefully this reply will reach you okay).

I spent a good while digging into this yesterday.

To recap, the problem is that when you enter a value for a Types date field (either in the back end or via a front-end CRED form) that gets saved in the database in a timestamp format in the local time (based on the site settings) without first converting it to UTC. (Timestamps, by definition, should be UTC.)

Ordinarily this doesn't affect most users, but it does affect you if you retrieve the date field value directly from wp_postmeta for some purpose and expect the date to be UTC-based.

My instinct was to intervene so that when the field is saved it is first converted to UTC, but it becomes too complicated trying to anticipate the various ways the value might be retrieved, when it would also need to be manipulated to restore the value back to the local time.

So I changed tack and decided that the best solution is to live with it being stored as a false timestamp (not UTC), and provide a generic solution to convert the field value to UTC after you have retrieved the value, so that you then know what you are dealing with.

Here's a function that should do that for you (it makes the adjustment based on the site timezone setting, although you can pass a value for the timezone if you want to use something else for some reason):

/**
 * Types date fields are stored as "timestamps" without first converting to UTC
 * Pass the stored value of a types date field through this function to convert to a UTC-based timestamp
 *   based on the site timezone setting (or a specified timezone setting such as "Europe/Paris")
 *   
 * Usage e.g. 
 * $value = get_postmeta( $id, 'wpcf-custom-date', true );
 * $valueUTC = types_date_to_utc( $value );
 * 
 */
function types_date_to_utc( int $timestamp, $timezone = "" ){

	if ( $timestamp ) {

		if ( empty ($timezone) ) {
			// get the site timezone
			$timezone = get_option("timezone_string");

			if ( empty($timezone) ){
				// use the offset instead
				$offset = get_option("gmt_offset");
				$timezone = sprintf("%+03d:00", $offset);
			}
		}

		$timezone_object = new DateTimeZone($timezone);
		$datetime = DateTime::createFromFormat('U', $timestamp);
		$offset = $timezone_object->getOffset($datetime);

		$timestamp = $timestamp - $offset;
	}

	return $timestamp;
}

It adjusts for what the time differential is at the point in time of the date. That is, if you passed a time from yesterday, it would know the differential then was 2 hours, but if you passed a date in November it would know that the differential then is 1 hour.

Is that something you can work with?

[Pat]

Hi Nigel,

Thanks for your help.
In fact, I'm using a function to make to conditional output.
Here it is :

/**
 * Shortcode pour calculer l'année en cours
 */

toolset_snippet_security_check() or die( 'Direct access is not allowed' );
function date_debut_ins( ) {
$id_va = 18207;
$datedebut = get_post_meta($id_va, 'wpcf-date-debut-inscription-site', true );
return $datedebut;
}
  add_shortcode( 'date-debut-ins', 'date_debut_ins' );

And I'm using this function in the conditional output :

[wpv-conditional if="( date_debut_ins() lte [post-today] ) AND ( date_fin_ins() gt [post-today] )" evaluate="true"]

What should I modify in this function in order to be sure to get the local time?
Regards
Pat

[Nigel]

>What should I modify in this function in order to be sure to get the local time?

But what's stored in the wp_postmeta is the local time. (It should be UTC, but it doesn't get converted to UTC before being saved.)

Your post-today shortcode returns the UTC timestamp for the current time. (It would return the same value regardless of timezone, the current UTC time.)

So you are comparing the post meta value (local time) with the current time (UTC).

You could add the register the function I shared above, and then use that function in your date_debut_ins function to convert the value retrieved with get_postmeta to UTC before comparing to the current UTC time.

[Pat]

Well Nigel,

Not so sure. I just check and the datefield saved thanks to the WP admin is not in local time. Here is a screenshot of the admin and the data I have found in the database for the field "wpcf-date-debut-inscription-site" : 1693818000 (which is 2 hours more than the local time !).
On the other hand, each time the field is displayed on the site, the local time is OK. Its only when used with this function in the conditional output that something is wrong.

Hope this helps to have a better understanding.
Regards
Pat

[Nigel]

In the post editor you introduce a date, 4 Sep 2023 09:00.

Your timezone is Paris, right? And that is currently (and on 4 Sep) UTC + 2.

So the corresponding UTC time is 4 Sep 2023 07:00.

Converting 4 Sep 2023 07:00 to a UTC timestamp gives 1693810800 (you can check with hidden link).

But what gets stored in wp_postmeta is 1693818000 (look closely, the two numbers are different, by 7200 seconds, or two hours).

So what is stored converted the Paris time of 4 Sep 2023 09:00 to a "timestamp" without first converting it to UTC. (It is not a true timestamp, which are always UTC, and the true timestamp 1693818000 corresponds to the Paris time of 4 Sep 2023 11:00.)

As noted, your post-today shortcode returns a true timestamp of the current time (meaning it is in UTC).

Hence the problem comes from comparing a true timestamp (of the current time) with a faux-timestamp (the date field converted to a timestamp from the local time).

And hence the proposed solution that your shortcodes, which get the date field values (with get_postmeta), should then pass those values through the function I shared which converts the faux-timestamps to true timestamps. You'll then be comparing a true timestamp with a true timestamp.

[Pat]

You're right Nigel,

The "post-today" function is taking care of the local time and not the debut-ins-site one.
Here is the post-today function code :

add_shortcode('post-today', 'today_shortcode');
function today_shortcode() {
date_default_timezone_set('Europe/Paris');
    $timestamp = time();
    return $timestamp;
}

Is there an easy way to add the date_default_timezone_set('Europe/Paris'); line somewhere in the debut-ins-site function code, just after the $datedebut = get_post_meta($id_va, 'wpcf-date-debut-inscription-site', true ); line ?

Regards
Pat

[Nigel]

In your post-today function the line that sets the default timezone doesn't achieve anything: time() will return the same value regardless of which timezone is set, because it generates a timestamp that is by definition UTC.

You need to include the definition of the types_date_to_utc function I provided earlier (include it above your other functions that will use it), and then update your date_debut_ins function and once you get $datedebut pass it through my types_date_to_utc function (note the example in its own commented documentation).

Then you will be comparing two UTC-based timestamps.

Do the same for your date_fin_ins shortcode.

[Pat]

Hi Nigel,

Sorry, I haven't got the time to make the test. I propose to close the ticket and come back with a new one if anything goes wrong?
Thanks
Pat