Convert seperate Intergers to Date-time format for Table

Next Question,

Now, thanks to Colby I have my PLC datafiles being grabbed by a Transaction block group and can view the data in a table without issue. I now face the challenge of converting some of the Database fields to be displayed in the table as a date and a time?

AB Datafiles for eg. are set as follows

N211:0 length 100 as the year integer
N212:0 length 100 as the Month Integer
N213:0 length 100 as the Day Integer
N214:0 length 100 as the Hour Integer
N215:0 length 100 as the Minute Integer
N216:0 length 100 as the Second Integer

Each of these datafiles are now a column in a Block transaction group.

Any help muchly appreciated…


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! :angry: 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:

  1. 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;
  1. 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:

  1. Select the maximum row index of the data table (“select max(datatable_ndx) from datatable”) . This is set to “run always”.
  2. 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!

Thanks again Colby,

I had to tweak the code a little to get it working with MS SQL. I now have a new column being updated with the formated Date and time. cool… :slight_smile: