1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Date & time conversion using auto actions.

[SOLVED] Date & time conversion using auto actions.

5 years 6 months ago - 5 years 6 months ago #307996 by RebeccaWool
[SOLVED] Date & time conversion using auto actions. was created by RebeccaWool
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:
select #__eb_events.event_date from #__eb_events, #__eb_registrants
where #__eb_events.id = #__eb_registrants.event_id
and #__eb_registrants.user_id = '[user_id]'
and #__eb_registrants.checked_in = 1
and #__eb_events.alias = 'LHPS'
order by #__eb_events.event_date desc
limit 1 ;

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.....
Life is a constant learning curve and I've just started out!

Please Log in to join the conversation.

5 years 6 months ago #308000 by krileon
Replied by krileon on topic Date & time conversion using auto actions.
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.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: nant, RebeccaWool

Please Log in to join the conversation.

5 years 6 months ago #308019 by RebeccaWool
Replied by RebeccaWool on topic Date & time conversion using auto actions.
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.
select #__eb_events.event_date from #__eb_events, #__eb_registrants
where #__eb_events.id = #__eb_registrants.event_id
and #__eb_registrants.user_id = '[user_id]'
and #__eb_registrants.checked_in = 1
and #__eb_events.alias = 'LHPS'
order by #__eb_events.event_date desc
limit 1

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:
update #__comprofiler
set cb_lhps_course_date =
[select #__eb_events.event_date from #__eb_events, #__eb_registrants
where #__eb_events.id = #__eb_registrants.event_id
and #__eb_registrants.user_id = '[user_id]'
and #__eb_registrants.checked_in = 1
and #__eb_events.alias = 'LHPS'
order by #__eb_events.event_date desc
limit 1]
where user_id = '[user_id]'

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.
Life is a constant learning curve and I've just started out!

Please Log in to join the conversation.

5 years 6 months ago #308021 by krileon
Replied by krileon on topic Date & time conversion using auto actions.
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.

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.
The following user(s) said Thank You: RebeccaWool

Please Log in to join the conversation.

5 years 6 months ago #308071 by RebeccaWool
Replied by RebeccaWool on topic SOLVED: Date & time conversion using auto actions.
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!
Life is a constant learning curve and I've just started out!
The following user(s) said Thank You: nant, krileon

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Date & time conversion using auto actions.
Time to create page: 0.202 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. [SOLVED] Date & time conversion using auto actions.

Search

Login / Logout

User Menu