Working with dates in Jestor

Dates formats and values

When working with dates inside Jestor, there's one thing to keep in mind that may not be immediately obvious when running a trigger for the first time: date formatting can differ depending on when and how you're getting that information.

  • If the value is already saved on Jestor's database, it'll have date formatting. This means if you're running a trigger as After a record is created/updated/deleted, or if you're getting the information through a Jestor.loadData() function, you'll get the information formatted as mm/dd/yyyy.
  • If the value is not yet saved on Jestor's database, it'll have a Unix timestamp value. This means if you're getting a value through user input in a Before a record is created/updated/deleted, it'll look something like 1628196834.
  • If the value is coming from a trigger, it'll keep the formatting you've set in the trigger. This means if you do something like $date = date('Y-m-d'), it'll have Y-m-d formatting.

🚧

Regional differences

If you're using Jestor in Brazilian Portuguese, standard date formatting on a field will be dd/mm/yyyy instead of mm/dd/yyyy, and you should take this into account when creating triggers and functions.

When you want to write a date onto a date field, you could pass the value as Unix timestamp or with regional formatting, but it's a good practice to always use dates in the standard ISO Y-m-d format when saving information, as it will always work regardless of the account's region. Also, filters in searches such as Jestor.loadData() will require Y-m-d formatting.

Manipulating dates

You can use standard PHP functions to manipulate dates however you need to, the three more commonly used functions being:

  • date($format, $value): this function takes a Unix timestamp value and converts it into a formatted date.
  • time(): this gets the Unix time value of the exact moment the function is called.
  • strtotime(): this function can be used either to turn a formatted date into a Unix timestamp or to get a Unix timestamp and manipulate it (such as adding X weekdays).

You can get more information on date(), time() and strtotime() here.

🚧

Regional differences

If you're using Jestor in Brazilian Portuguese, be aware that strtotime() will not convert dd/mm/yyyy dates properly.

A common practice would be to convert the format to dd-mm-yyyy with a str_replace() function, which will then be accepted in strtotime() properly.

Example: strtotime(str_replace('/','-',$braziliandate));

Common examples

Below are some examples taking into consideration common situations regarding dates in Jestor:

#When using a Before a record is created trigger to add 1 week to a date.

$date = $objectNew['meeting_date'];
$date = strtotime('+1 week',$date);

#When using an After a record is created trigger to add 1 week to a date.

$date = $objectNew['meeting_date'];
$date = strtotime('+1 week',strtotime($date));

#Searching for a record in Jestor and using a value from a date field.

$search = Jestor.loadData('table_name',['where' => ['id_table_name' => $id]]);
$date = search[0]['meeting_date'];
$date = strtotime('+1 week',strtotime($date));