Cotonti Extra fields: datetime data type — Date and time

Cotonti Extra Fields: datetime Data Type — Date and Time 

Overview 

The datetime type in Cotonti's extra fields system is designed to store date and time as an integer — a Unix timestamp. This value represents the number of seconds since midnight on January 1, 1970, UTC. This approach ensures compact storage, easy comparison, and correct conversion between time zones. 

All logic for handling datetime fields is concentrated in three main files: 

• system/extrafields.php — functions for building forms, importing and outputting data. 
• system/forms.php — generation of date select dropdowns (cot_selectbox_date). 
• system/functions.php — helper functions for date and time handling (cot_import_date, cot_date, cot_mktime, cot_date2stamp, cot_stamp2date). 

Below we will examine each aspect of working with this type in detail. 

Storing Values in the Database 

When an extra field of type datetime is created via cot_extrafield_add(), a column is added to the database table with the following SQL type: 

int DEFAULT '0'

This means:

• The value is stored as an integer (Unix timestamp).
• An empty value (date not selected) corresponds to the number 0.
• The maximum value is defined by the int type (usually 2^31-1), which allows dates up to 2038 on 32-bit systems, but in most modern environments 64-bit PHP is used, providing a wider range.

In practice, the database always contains either 0 or a positive integer — the number of seconds since the epoch.

Field Parameters (field_params)

The field_params field in the cot_extra_fields table for datetime stores a string consisting of three comma-separated components:

min,max,format

• min — minimum year (integer). Used to limit the year range in the edit form and during import.
• max — maximum year (integer).
• format — date/time format string for output. Can be a standard PHP format (e.g., 'Y-m-d H:i') or a Cotonti localized format key (e.g., 'datetime_medium').

Default Behavior

If parameters are not set or only partially set, the following rules apply:

• If min is empty or ≤ 0, 2000 is used.
• If max is empty or ≤ 0, 2030 is used.
• If format is empty, the raw Unix timestamp is returned on output without formatting.

Parsing Parameters in the Code

In the cot_build_extrafields() function (for building the form), parameters are parsed as follows:

$extrafield['field_params'] = str_replace([' , ', ', ', ' ,'], ',', $extrafield['field_params']);
list($min, $max, $format) = explode(",", $extrafield['field_params'], 3);
$max = (int)$max > 0 ? $max : 2030;
$min = (int)$min > 0 ? $min : 2000;

In the import function cot_import_extrafields(), only the first two parameters are parsed:

list($min, $max) = explode(",", $extrafield['field_params'], 2);

In the output function cot_build_extrafields_data():

list($min, $max, $format) = explode(",", $extrafield['field_params'], 3);
return (empty($format)) ? $value : cot_date($format, $value);

Thus, the third parameter (format) is used exclusively for output. It is ignored during import and form building.

Building the Edit Form

The date/time input form is generated by the cot_build_extrafields() function in the case 'datetime' branch.

Algorithm of cot_build_extrafields for datetime

1. Initialize global variables: global $sys;
2. Clean parameters: remove extra spaces around commas.
3. Parse parameters: extract $min, $max, $format.
4. Set default values: if $max ≤ 0, use 2030; if $min ≤ 0, use 2000.

5. Handle relative dates: if the value $data starts with + or -, it is interpreted as an offset in seconds from the current time $sys['now']:

   $data = (mb_substr($data, 0, 1) == "+") ? $sys['now'] + (int)(mb_substr($data, 1)) : $data;
   $data = (mb_substr($data, 0, 1) == "-") ? $sys['now'] - (int)(mb_substr($data, 1)) : $data;

   This allows values like +86400 (tomorrow) or -3600 (one hour ago).

6. Call the date generator: cot_selectbox_date((int)$data, 'long', $name, (int)$max, (int)$min, true, $extrafield['field_html']).

Function cot_selectbox_date()

Let's examine its behaviour based on the code in system/forms.php.

Parameters

• $utime — Unix timestamp of the selected date.
• $mode — display mode. For extra fields, 'long' is always passed (full set: year, month, day, hour, minute).
• $name — base field name (e.g., rxtra_x200_last_promotion).
• $max_year, $min_year — year boundaries.
• $usertimezone — flag to account for the user's timezone. For extra fields, true is passed.
• $custom_rc — custom resource template.

Execution Steps

1. Check for custom function: if cot_selectbox_date_custom() is defined, it is called and the result is returned without further action.
2. Hook form.date: developers can override output via plugins hooked to this event.
3. Determine resource name: if the field name contains square brackets (e.g., rxtra_date[user_id]), the base name before the brackets is extracted for resource string lookup.

4. Adjust time for timezone:

   $utime = ($usertimezone && $utime > 0) ? ($utime + $usr['timezone'] * 3600) : $utime;

   If $utime is greater than zero and timezone adjustment is enabled, the current user's offset (in hours, multiplied by 3600) is added. This ensures the time is displayed in the user's local zone.

5. Determine date components:
   • If $utime == 0 (empty date), the components ($s_year, $s_month, $s_day, $s_hour, $s_minute) are set to null. Then the buffered value is checked via cot_import_buffered(). This allows restoring selected values after a failed form submission (e.g., validation error). If the buffer contains an array, its values are used to fill the fields.
   • If $utime > 0, components are extracted via date('Y-m-d-H-i', $utime) and split by -.
6. Build month array: uses the language array $L for localized month names (from $L['January'] to $L['December']).
7. Create dropdowns:
   • Year: cot_selectbox($s_year, $name.'[year]', range($max_year, $min_year, -1)). Range from max to min.
   • Month: cot_selectbox($s_month, $name.'[month]', array_keys($months), array_values($months)).
   • Day: cot_selectbox($s_day, $name.'[day]', range(1, 31)).
   • Hour: cot_selectbox($s_hour, $name.'[hour]', range(0, 23)) with leading zeros via sprintf('%02d', $i).
   • Minute: cot_selectbox($s_minute, $name.'[minute]', range(0, 59)).
8. Select resource template: first looks for $R["input_date_{$mode}"], then $R["input_date_{$rc_name}"], then $custom_rc, otherwise 'input_date'.
9. Assemble HTML: via cot_rc() passing the generated lists.

The result is a form containing five sequential <select> elements, usually wrapped in a container from the input_date resource file.

Importing and Saving Values

Importing data from the form is handled by the cot_import_extrafields() function in the case 'datetime' branch.

Import Steps

1. Clean parameters: remove spaces around commas.
2. Parse min and max (third parameter is not used).
3. Call cot_import_date($inputname, true, false, $source):
   • $inputname — field name (e.g., rxtra_x200_last_promotion).
   • true — account for user's timezone.
   • false — do not return an array, return a timestamp directly.
   • $source — source ('P' for POST).
4. Handle the result of cot_import_date():
   • If the function returned null (empty field), set $import = 0.

   • If min or max are set (greater than 0), adjust the year:

     list($s_year, $s_month, $s_day, $s_hour, $s_minute) = explode('-', @date('Y-m-d-H-i', $import));
     if ($min > $s_year) {
         $import = mktime($s_hour, $s_minute, 0, $s_month, $s_day, $min);
     }
     if ($max < $s_year) {
         $import = mktime($s_hour, $s_minute, 0, $s_month, $s_day, $max);
     }

     Thus, if the selected year falls outside the allowed bounds, it is forcibly replaced with min or max while preserving the other components.

5. Check required status: if the field is required (field_required = 1) and $import is null, '' or 0, an error is triggered via cot_error().

Function cot_import_date()

This function is located in system/functions.php and plays a key role.

Logic

1. Check for custom function: if cot_import_date_custom() exists, it is called.
2. Hook import.date: allows plugins to override import.
3. Retrieve data:
   • First attempts to get an array from the source via cot_import($name, $source, 'ARR'). Expects an array with keys year, month, day, hour, minute.
   • If the array is empty, attempts to read the value as a plain string (cot_import(..., 'TXT')). This can be a date in text format (e.g., 2025-12-31 23:59).
4. Handle string value:
   • If the string is not empty, cot_date2stamp($date) is called to convert it to a timestamp. If conversion fails, null is returned.
5. Handle array:
   • Array components are checked for presence; missing ones are set to 0.
   • If all components are null (the form field was submitted but no element selected), null is returned.
   • If the condition ($month && $day && $year) || ($day && $minute) is true, a timestamp is created via cot_mktime($hour, $minute, 0, $month, $day, $year).
   • Otherwise, the string and format keys (which may be passed in the array) are checked. If the string is not empty, cot_date2stamp($string, $format) is used. Otherwise null is returned.

6. Adjust timezone:

   if ($usertimezone) {
       $timestamp -= Cot::$usr['timezone'] * 3600;
   }

   The current user's timezone offset is subtracted from the timestamp to convert it to UTC before saving to the database.

7. Return result:
   • If $returnarray = true, an array with keys stamp, year, month, day, hour, minute is returned.
   • Otherwise, an integer timestamp is returned.

Thus, the final value stored in the database is always a UTC timestamp.

Outputting Values in Templates

To obtain a ready-to-display value for an extra field, the cot_build_extrafields_data() function (in system/extrafields.php) is used.

Behavior for datetime

case 'datetime':
    $extrafield['field_params'] = str_replace([' , ', ', ', ' ,'], ',', $extrafield['field_params']);
    list($min, $max, $format) = explode(",", $extrafield['field_params'], 3);
    return (empty($format)) ? $value : cot_date($format, $value);
    break;

• Only the third component — format — is extracted from the parameters.
• If format is empty, the raw $value (timestamp) is returned.
• If format is set, the cot_date($format, $value) function is called.

Function cot_date()

Located in system/functions.php. Used for localized date formatting.

function cot_date($format, $timestamp = null, $usertimezone = true)
{
    global $lang, $Ldt;
    if (is_null($timestamp)) {
        $timestamp = Cot::$sys['now'];
    }
    $timestamp = (int) $timestamp;
    if ($usertimezone) {
        $timestamp += Cot::$usr['timezone'] * 3600;
    }
    $datetime = (isset($Ldt[$format])) ? @date($Ldt[$format], $timestamp) : @date($format, $timestamp);
    // ... replace English month/day names with localized versions
    return ($lang == 'en') ? $datetime : str_replace($search, $replace, $datetime);
}

• If $timestamp is not passed, the current time Cot::$sys['now'] is used.
• When $usertimezone = true, the user's timezone offset (Cot::$usr['timezone'] * 3600) is added to the timestamp, converting UTC to local time for display.
• If the format exists in the $Ldt array (localized formats), it is used; otherwise, the passed PHP format is used.
• After formatting, English day and month names are replaced with localized versions from $L (if the language is not English).

Timezone Handling

A key feature of the datetime type is correct handling of user time zones. The scheme is as follows:

1. On input (import):
   • The user selects a date and time in their local zone.
   • cot_import_date() subtracts their timezone offset (Cot::$usr['timezone'] * 3600), converting the time to UTC.
   • The UTC timestamp is saved to the database.
2. On output:
   • The UTC timestamp is retrieved from the database.
   • cot_date() adds the current user's timezone offset, converting it back to local time.
   • Then formatting and localization are applied.

Thus, each user sees the date and time in their own zone, regardless of where the record was saved.

The user's timezone is stored in Cot::$usr['timezone'] as an offset in hours from UTC (e.g., 3 for Moscow).

Using in Plugins and Modules

Registering a Field

To create a datetime extra field in a plugin, the cot_extrafield_add() function is used. Example:

cot_extrafield_add(
    'users',                    // table to which the field is added
    'my_event_datetime',        // field name (without table prefix)
    'datetime',                 // type
    '',                         // HTML construction (empty for default)
    '',                         // variants (not used for datetime)
    '',                         // default value (timestamp or 0)
    false,                      // required
    'HTML',                     // parser (does not affect datetime)
    'Event date',               // description
    '2000,2030,datetime_medium' // parameters: min,max,format
);

After this call, the cot_users table will have a column user_my_event_datetime with type int DEFAULT '0'.

Saving a Value

In a plugin, when handling a POST request:

$exfld = [/* array of field description obtained via cot_load_extrafields() */];
$oldValue = $existingData['user_my_event_datetime'] ?? 0;
$newValue = cot_import_extrafields('rxtra_my_event_datetime', $exfld, 'P', $oldValue, 'xtra_');
// $newValue — UTC timestamp or 0

If the field is required, the function will automatically generate an error for an empty value.

After successfully importing all fields, cot_extrafield_movefiles() is usually called (for file fields, but does not affect datetime).

Retrieving a Value

To get the value from the database, a plain SQL query or the plugin's API can be used. The value will be an integer (timestamp).

Using in Templates

Automatic Tag Generation

For users, Cotonti provides the cot_generate_usertags() function, which creates tags for all extra fields, including datetime.

For example, for the field user_x200_last_promotion, the following tags will be created:

• {USERS_DETAILS_XTRA_X200_LAST_PROMOTION} — formatted value (if format is set) or timestamp.
• {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_TITLE} — field label.
• {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE} — raw value (timestamp).

Note that in the standard tag generation for user extra fields, the prefix user_ is used, but it is not included in the tag name. In plugins like xtradbrowusers, there may be their own conventions.

Checking for a Value

Since an empty date is stored as 0, the _VALUE tag should be used to check if the field is filled:

<!-- IF {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE} -->
    <div class="contact-label">{USERS_DETAILS_XTRA_X200_LAST_PROMOTION_TITLE}</div>
    <div class="contact-value">{USERS_DETAILS_XTRA_X200_LAST_PROMOTION}</div>
<!-- ENDIF -->

The condition <!-- IF {TAG_VALUE} --> is true if the value is not empty and not equal to the string "0". This approach ensures that the block with the date will not appear if no date is selected.

Alternative Check with Explicit Comparison

<!-- IF {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE} > 0 -->
    ... output ...
<!-- ENDIF -->

Both methods are equivalent for datetime, since an empty value is always 0. Explicit comparison may be useful when comparing with the current date or other values.

Outputting Date in a Custom Format

If no format is specified in the field parameters, you can format the timestamp directly in the template using the PHP date function inside {PHP}:

{PHP|cot_date('d.m.Y H:i', {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE})}
or
{USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE|cot_date('d.m.Y H:i', $this)}

But usually the format is set in field_params, and then the regular tag already returns a formatted string.

Complete Block Example

<!-- IF {USERS_DETAILS_XTRA_X200_LAST_PROMOTION_VALUE} -->
<div class="d-flex align-items-center mb-3">
    <div class="contact-icon calendar me-3">
        <i class="fa-solid fa-calendar-alt fa-xl"></i>
    </div>
    <div>
        <div class="contact-label">{USERS_DETAILS_XTRA_X200_LAST_PROMOTION_TITLE}</div>
        <div class="contact-value">{USERS_DETAILS_XTRA_X200_LAST_PROMOTION}</div>
    </div>
</div>
<!-- ENDIF -->

Peculiarities and Pitfalls

Zero Value (0)

• An empty date is always stored as 0.
• When outputting with a format set, cot_date() will return January 1, 1970, which may look like "01.01.1970". Therefore, always check for a value using _VALUE before outputting.
• When checking required fields during import, 0 is considered an empty value.

Relative Dates

If a value in the database somehow contains a leading + or - (e.g., +86400), it will be converted to $sys['now'] + 86400 when building the form. This is convenient for dynamic deadlines but requires caution: when saving such a value through the form, it will be fixed as a concrete date.

Year Correction via min/max

• min and max affect the year dropdown in the form, as well as import: if the selected year is less than min, it is replaced with min; if greater than max, replaced with max.
• These parameters do not restrict values already stored in the database if they were modified manually.

Timezone

• On import, the time is adjusted according to the current user's timezone (the one filling the form).
• On output, the timezone of the user viewing the page is used.
• If the user is not logged in, Cot::$usr['timezone'] may be 0 or the default, usually UTC.

String Parsing

cot_import_date() supports importing not only from an array of components but also from a plain date string. This can be useful when integrating with external data sources. However, string input is not used in the standard form.

Hooks

• The form.date hook allows complete replacement of the date form output.
• The import.date hook allows overriding the import logic.
• If the cot_selectbox_date_custom() function is defined, it takes precedence.

Impact of Required Flag

If a datetime field is marked as required, an empty value (0) will trigger a validation error. The error will be associated with the field name, allowing a message to be displayed next to the relevant part of the form.

Conclusion

The datetime type in Cotonti is a powerful tool for storing dates and times with timezone awareness. It stores data as a UTC timestamp, provides convenient input via dropdowns, and flexible localized output. When using it in templates, always remember to check the value via _VALUE to correctly hide blocks with empty dates.

Plugin developers can fully control the field's behaviour through the min, max, and format parameters, and can use hooks to extend functionality. Knowing the details of cot_build_extrafields, cot_import_extrafields, cot_selectbox_date, and cot_date enables efficient integration of this type into any modules and themes.

This article is based solely on the analysis of the Cotonti source code, and contains no assumptions or unverified information.

Back to top