Custom Voicemessage MySQL Column

Asterisk Asterisk Support
#1

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?

#2

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?
#3

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

#4

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

#5

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
#6

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

#7

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
#8

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