Custom Voicemessage MySQL Column

Hi Guys,

I have successfully enabled my asterisk server to save voicemessages in wav format to an external MySQL database. And also, for easier voicemail access, I have created a custom PHP Webpage where my employees can access that page and play all the voicemessages in the database. From what I can see so far, That default MySQL Database lacks Time and Date Column.
My question is, Is it possible to create a custom MySQL column (In my case “Date and Time”) where each time a new voicemessage that is inserted by Asterisk into that Database it also inserts the time and date the voicemessage was created?

I’ve never stored VM in a database so I have no actual experience, but a peek at the source shows:

snprintf(sql, sizeof(sql), "INSERT INTO %s (dir,msgnum,recording,context,macrocontext,callerid,origtime,duration,mailboxuser,mailboxcontext,flag,msg_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)", odbc_table);

which kind of implies a fixed list of column names.

  1. Doesn’t ‘origtime’ contain what you want?
  2. Could you add a new column with the ‘default current_timestamp’ attribute?

Hi @sedwards, Thanks for your reply.
The origtime column contains what I think is somewhat random numbers only like 1626026380,1625638036, etc. It doesn’t have the slightest hint of a time and date. Also, I tried adding a custom column in voicemessages table like datetime default value NOW() but that doesn’t work. The weird thing is when I play the VMs via an extension it plays the time and date it was created so I assume that asterisk stores it somewhere

You can write MySQL function to auto update value when new row inserted

Hi, @aysman.

The value 1625638036 is a date/time in the UNIX timestamp format.

This value corresponds to 07/12/2021 @ 7:45am [UTC]

More info:

1 Like

Or use the ‘on update current_timestamp’ attribute when the file is created.

In MySQL

$ mysql --execute="select from_unixtime(1626026380, '%a %b %d %T PDT %Y') as origtime;"
+------------------------------+
| origtime                     |
+------------------------------+
| Sun Jul 11 10:59:40 PDT 2021 |
+------------------------------+

In shell:

$ date --date=@1626026380
Sun Jul 11 10:59:40 PDT 2021

Wow I learned something new today. Thanks to all of you guys who replied to my query. Cheers

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.