Where to find Time Groups outside of the GUI?

Probably a very simple answer, but I want to extract the time groups. Is this in a handy file somewhere like the trunk definitions are?

I’d like to design something that will a week before each federal (US) holiday send out an email saying “We’ll be closed on ____ day, so if you need to get ahold of us during the day send us an email. It looks like you’ll be open / closed / closing early / etc that day. If this is not correct let us know ASAP”.

Ideally I’d do this with a script so I don’t have to enter the holidays multiple times (Outlook, CRM program, whatever I end up using to generate emails).

You can extract those details from mysql;

mysql -u user -ppassword -D asterisk -e “SELECT * from timegroups_groups”


mysql -u user -ppassword -D asterisk -e “SELECT * from timegroups_details”

How you identify your “holiday” groups would be up to you, but then something like:-

mysql -u user -ppassword -D asterisk -e “SELECT description,time FROM timegroups_details LEFT JOIN timegroups_groups ON timegroups_details.timegroupid=timegroups_groups.id WHERE timegroups_groups.description=‘Holiday’”

might return something like

> +-------------+-------------------------------+
> | description | time                          |
> +-------------+-------------------------------+
> | Holiday     | 00:00-07:59|mon|29|dec        |
> | Holiday     | 00:00-23:59|wed-sun|24-28|dec |
> | Holiday     | 16:55-23:59|tue|23|dec        |

You could take it from there .

Excellent, thank you. I’ll give it a try shortly.

Have fun with Easter, you might need Pascal for that :wink:

I would search for freepbx bootstrap in the wiki then look at natime functions

Holidays have always amused me,

The problem with Federal (or any “movable feast”) is how you script it , as a reference:-


for example for Veterans day, to cover all eventualities you would need:-


and it would be trivial to add State wide Public Holidays as pertinent

I suggest you start with

and as James suggests you can add/read them either with the somewhat undocumented FreePBX PHP wrapper or directly with mysql queries/updates into asterisk.timegroups_groups and asterisk.timegroups_details, I’m pretty sure either way would work, of course I prefer my way as to me it is more succinct :slight_smile:

And then you would need to identify the earliest occurence of each "holiday’ , subtract 7 days and then send your email, I suggest easier in mysql than otherwise.


When you try to script them all, as I said Easter is a problem, for “Western” Easter in the Gregorian calendar , then add to you asterisk database in mysql a function:-

drop function easter_date;
    Delimiter // 

    CREATE FUNCTION easter_date(year_date DATETIME) 

      DECLARE a INT;
      DECLARE b INT;
      DECLARE c INT;
      DECLARE d INT;
      DECLARE e INT;
      DECLARE f INT;
      DECLARE g INT;
      DECLARE h INT;
      DECLARE i INT;
      DECLARE k INT;
      DECLARE l INT;
      DECLARE m INT;
      DECLARE n INT;
      DECLARE p INT;
      DECLARE x INT;

      set x = YEAR(year_date);
      set a = x % 19;
      set b = TRUNCATE(x / 100, 0);
      set c = x % 100;
      set d = TRUNCATE(b / 4, 0);
      set e = b % 4;
      set f = TRUNCATE((b + 8) / 25, 0);
      set g = TRUNCATE(((b - f) + 1) / 3, 0);
      set h = (((((19 * a) + b) - d) - g) + 15) % 30;
      set i = TRUNCATE(c / 4, 0);
      set k = c % 4;
      set l = (32 + 2 * e + 2 * i - h - k) % 7;
      set m = TRUNCATE((a + 11 * h + 22 * l) / 451, 0);
      set n = TRUNCATE((h + l - 7 * m + 114) / 31, 0);
      set p = ((((h + l) - (7 * m)) + 114) % 31) + 1;

      return CONCAT(x,"-",n,"-",p);


select LOWER(DATE_FORMAT(DATE(easter_date(‘2015-1-1’)),‘00:00-23:59|%a|%e|%b’));

will give you a formatted FreePBX timecondition for Easter in 2015, you would need to update that before the first moonrise after nehelim 14 or somesuch, or maybe just before March is easier :slight_smile:


Some things require a degree in mathematics :-/

And for a more global view