Time Zone and hour format problem

Hi @ads97129,

I tested the calendar and events list display with the dates and timezone, and I couldn’t replicate this on my end. To assist with further troubleshooting, would you please confirm how you have the timezone settings on your end, under Sugar Calendar » Settings, under the Time Zone section?

Regarding the hour format, I confirmed the issue with the format G\hi, and I’ve shared this with the dev team. I’ll be in touch with an update on this.

Kind regards.

Hi @bradley & @ads97129,

Thanks for your continued patience.

I received an update from the dev team, and while we had a challenge replicating the issue as described, we narrowed down the problem to the query and included a potential fix. To fix the issue, could you please replace the current snippet with this one and let me know if it works?

// Fixes Upcoming Events Block timezone issues.
add_filter( 'sugar_calendar_helpers_get_upcoming_events_list_with_recurring', 'sc_lite_fix_missing_events_in_upcoming_events_block', 10, 3 );

/**
 * Implements the fix for issues with upcoming events timezone.
 *
 * @param Event[]|false $upcoming_events The upcoming events list with recurring events.
 * @param array         $args            The arguments to get the events.
 * @param array         $attributes      The block attributes.
 *
 * @return Event[]
 */
function sc_lite_fix_missing_events_in_upcoming_events_block( $upcoming_events, $args, $attributes ) {

	if (
		! function_exists( 'sugar_calendar' ) ||
		sugar_calendar()->is_pro()
	) {
		return $upcoming_events;
	}

    global $wpdb;

    $calendars_left_join = '';
    $where_calendars     = '';

    // Get the category left join and where queries if necessary.
    if ( ! empty( $args['calendar_ids'] ) ) {

        if ( ! is_array( $args['calendar_ids'] ) ) {
            $args['calendar_ids'] = [ $args['calendar_ids'] ];
        }

        $term_taxonomy_ids = array_filter( array_map( 'absint', $args['calendar_ids'] ) );

        if ( ! empty( $term_taxonomy_ids ) ) {
            $calendars_left_join = 'LEFT JOIN ' . $wpdb->term_relationships . ' AS cal_terms ON ' . $wpdb->prefix . 'sc_events.object_id = cal_terms.object_id';
            $where_calendars     = $wpdb->prepare(
                'AND ( cal_terms.term_taxonomy_id IN (%1$s) )',
                implode( ',', $term_taxonomy_ids )
            );
        }
    }

    $select_query = 'SELECT ' . $wpdb->prefix . 'sc_events.id FROM ' . $wpdb->prefix . 'sc_events';

    if ( ! empty( $calendars_left_join ) ) {
        $select_query .= ' ' . $calendars_left_join;
    }

    $tz  = sugar_calendar_get_timezone_type() === 'off' ? 'UTC' : sugar_calendar_get_timezone();
	$tz  = $tz === null ? 'UTC' : $tz;
    $now = sc_lit_fix_get_request_time( 'mysql', $tz );

    $today = gmdate( 'Y-m-d 00:00:00', strtotime( $now ) );

    $where_query = $wpdb->prepare(
        'WHERE ' . $wpdb->prefix . 'sc_events.status = "publish" AND ' . $wpdb->prefix . 'sc_events.object_subtype = "sc_event" AND '
        . $wpdb->prefix . 'sc_events.start >= %s AND ' . $wpdb->prefix . 'sc_events.end >= %s',
        $today,
        $now
    );

    if ( ! empty( $where_calendars ) ) {
        $where_query .= ' ' . $where_calendars;
    }

    if ( ! empty( $args['search'] ) ) {
        $where_query .= $wpdb->prepare(
            ' AND ' . $wpdb->prefix . 'sc_events.title LIKE %s',
            '%' . $wpdb->esc_like( $args['search'] ) . '%'
        );
    }

    // Add tags filter.
    if ( ! empty( $attributes['tags'] ) ) {

        $tag_ids = array_filter( array_map( 'absint', $attributes['tags'] ) );

        if ( ! empty( $tag_ids ) ) {
            // Change the SELECT query to include tag term taxonomy ID.
            $select_query = 'SELECT ' . $wpdb->prefix . 'sc_events.id, tag_terms.term_taxonomy_id AS tag_term_taxonomy_id FROM ' . $wpdb->prefix . 'sc_events';

            // Add tag JOIN - using standard WordPress term relationships table.
            $tag_taxonomy_id = Sugar_Calendar\Features\Tags\Common\Helpers::get_tags_taxonomy_id();

            // Create tag-specific join clause.
            $tags_join  = 'INNER JOIN ' . $wpdb->term_relationships . ' AS tag_terms ON ' . $wpdb->prefix . 'sc_events.object_id = tag_terms.object_id';
            $tags_join .= ' INNER JOIN ' . $wpdb->term_taxonomy . ' AS tag_taxonomy ON tag_terms.term_taxonomy_id = tag_taxonomy.term_taxonomy_id';

            // Combine joins - if calendar join exists, append to it, otherwise use tag join.
            if ( ! empty( $calendars_left_join ) ) {
                $combined_join = $calendars_left_join . ' ' . $tags_join;
            } else {
                $combined_join = $tags_join;
            }

            // Update SELECT query with combined JOIN clause.
            $select_query .= ' ' . $combined_join;

            // Add tag WHERE clause - filter by taxonomy and tag IDs.
            $placeholders = implode( ',', array_fill( 0, count( $tag_ids ), '%d' ) );
            $where_query .= $wpdb->prepare(
                ' AND tag_taxonomy.taxonomy = %s AND tag_taxonomy.term_id IN (' . $placeholders . ')',
                array_merge( [ $tag_taxonomy_id ], $tag_ids )
            );
        }
    }

    $order_by = $wpdb->prepare(
        'ORDER BY ' . $wpdb->prefix . 'sc_events.start ASC LIMIT %d OFFSET %d',
        $args['number'],
        $args['offset']
    );

    $final_query = $select_query . ' ' . $where_query . ' ' . $order_by;

    // The query is prepared/sanitized individually.
    $event_ids = $wpdb->get_results( $final_query );

    if ( empty( $event_ids ) ) {
        return [];
    }

    $sugar_calendar_events_args = [
        'id__in'  => wp_list_pluck( $event_ids, 'id' ),
        'orderby' => 'start',
        'order'   => 'ASC',
    ];

    return sugar_calendar_get_events( $sugar_calendar_events_args );
}

/**
 * Implements the fix helper.
 *
 * @param string $type     The type of time to return.
 * @param string $timezone The timezone to use.
 *
 * @return int The time in the requested format.
 */
function sc_lit_fix_get_request_time( $type = 'timestamp', $timezone = 'UTC' ) {

	global $timestart;

	// Get timestart, avoid overriding the global.
	$current_timestart = absint( empty( $timestart ) ? microtime( true ) : $timestart );

	// Ensure that timezone is set.
	$timezone = empty( $timezone ) ? 'UTC' : $timezone;

	// Get the offset if not UTC.
	$is_utc = $timezone === 'UTC';

	if ( ! $is_utc ) {

		$offset = (int) sugar_calendar_get_timezone_offset(
			[
				'time'     => $current_timestart,
				'timezone' => $timezone,
				'format'   => 'seconds',
			]
		);
	}

	// What type of.
	switch ( $type ) {
		// 'Y-m-d H:i:s'.
		case 'mysql':
		case 'Y-m-d H:i:s':
			$retval = $is_utc
				? gmdate( 'Y-m-d H:i:s' )
				: gmdate( 'Y-m-d H:i:s', ( $current_timestart + $offset ) );
			break;

		// Unix timestamp.
		case 'timestamp':
		case '':
			$retval = $is_utc
				? $current_timestart
				: $current_timestart + $offset;
			break;

		// Mixed string.
		default:
			$retval = $is_utc
				? gmdate( $type )
				: gmdate( $type, ( $current_timestart + $offset ) );
			break;
	}

	if ( is_numeric( $retval ) ) {
		$retval = intval( $retval );
	}

	return $retval;
}

To clarify, with this snippet, the block will query events based on the timezone setting under your Sugar Calendar » Settings section.

Let me know how it goes.

Regards

Hi @bradley,

Thanks for getting back to me and for the update.

It’s possible that this might be specific to your setup, as we’re having difficulty replicating it on our end.

To assist in narrowing down your setup, could you please open a support ticket and share the events export from your site. You can export this from your WordPress dashboard, Sugar Calendar » Tools page, under the Export tab)?

Thanks, and I look forward to having this resolved.