[SOLVED] Date & time conversion using auto actions.

Hello CB Team,

I require help with the following situation please:

Column 'events_date' in table eb`_events produces a date & time record which I have queried using CB Query Field:

This returns something like:

2018-10-17 09:30:00

Now, I require the following from that result.

1. Convert to date only in the format of 'd/m/y'
2. Increase the date by a set amount of months, for example 12
3. Store the result in a column within #__comprofiler table for access within the CB profile

I believe CB Auto Actions is the way forward with this but I can't for the life of me work out how to do it.
Ultimately this process will have to be carried out on over 200 results where the alias from the first query will be different but I don't mind repeating the process of entering the Auto Actions manually to accomplish the desired end result.

Do you feel that this is achievable using CB Auto Actions and if so, any pointers on how to do it will be gratefully received.

Cheers.....

Only way to do what you're wanting is to do it in the query it self. You can format dates from SQL as well as add/subtract minutes, hours, etc.. to/from them using SQL functions. Recommend reviewing the below MySQL documentation.

dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html

Query fields don't store anything as they just display the results of your query so CB Auto Actions can't really do anything here.

Hello Kyle,

I have "looked" at the suggested documentation: dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html but have zero idea of what the heck is going on with that page.

So, I created the following query which works and returns the date and time when used in a CB query field.

I really want to update cb_lhps_course_date in #__comprofiler by using Auto Actions so that I can manipulate the data more easily.

I did some test code with simple update and set code in Auto Actions and had it updating the DB when the user viewed their profile and logged in.

So, I now have a query inAuto Actions:

But it does not update the relevant record in the DB.
I read this post: www.joomlapolis.com/forum/153-professional-member-support/228217-custom-auto-action-on-after-user-save
Please help, it's driving me mad!

Cheers.

You can't update the field because a CB Query Field doesn't store anything for CB Auto Actions to update. Your second block of code isn't a valid query as you're surrounding your subquery with [ and ] instead of ( and ); you also need to be trying to store this to a field that has storage (e.g. a datetime field). As for the documentation the below functions would be relevant to you.

Format:
dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-format

Add Time:
dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-add

Subtract Time:
dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_date-sub

All 3 of the above have examples of how they work. You'd be using these functions on your event_date column.

Hello Kyle,

Many thanks for your assistance with all of this, I found it so confusing but have now sorted it to the point of getting the results I need.

CB Auto Actions is a fantastic tool for use with Joomla, thanks!