As one of the programmers here, I hate when stuff like this comes up, because I know there’s a feature that hasn’t yet been implemented that would make this really easy! Not to fear though, because there’s always (at least) 2 ways to do things.
First, the (non) feature I’m referring to: groups can have expression items in them, which reference other items. Block groups can have expressions too. Unfortunately, it’s not possible to reference the “block items”. If you could, you could just create one expression that referenced the other items and was calculated for each row.
Ok, so now for real options:
- Depending on how you need to use it, you may choose to only convert it when querying, for display. There are probably a few different ways to do this, but with mysql this would work (I’ve made up short column names, hopefully it’s clear):
select cast(concat(yr,'-',mt,'-',dy,' ',hr,':',mi,':',sc) as datetime) from datatable;
- If you really wanted to store it, you could just do that in an update query. You could either manually at a datetime column to the existing table, or create a new table. I’ll assume you’ll add a new column. The column default would be null. At some point (after new rows are inserted), you would run the following query to populate it:
update datatable set datecolumn = cast(concat(yr,'-',mt,'-',dy,' ',hr,':',mi,':',sc) as datetime) where datecolumn is null
As for when to run it, the easiest thing to do would be to create another transaction group, set to update the first row of a “dummy” table (it won’t actually interact with the database in the normal way). Create 2 expressions:
- Select the maximum row index of the data table (“select max(datatable_ndx) from datatable”) . This is set to “run always”.
- A “on trigger” item that has that update query in it.
Set the group to trigger “on change” of the 1st item.
If you want to be super fancy, you could avoid this second group by setting up an “on-insert” database trigger to do the calculation for you. You can look up the syntax for this by searching for “mysql create trigger”. I tend to shy away from this type of thing because I don’t like having extra logic in the db- it makes it harder for someone else to track down how things work (and must be carefully managed when restoring failed systems).
Hope this helps!