I am trying to: Show a datatable (WPDatatables) using a cred form entry via a "Generate a query to WordPress database. Create a MySQL-query-based table by generating a query to WordPress database (posts, taxonomies, postmeta) with a GUI tool."
Link to a page where the issue can be seen: versteckter Link
I expected to see: A regular date and time in the datatable columns
Instead, I got: Unix Date because I used the date picker which apparently then places it in the DB in unix format. Is there a way to change this so it displays it as a regular date (or posts it to the DB as a regular date)? I know that I could use a view to display it correctly which means that there must be some conversion going on in a "content view" for it to do that. But WPDatatables is a great plugin and has excellent uses for toolset, so I am wondering if there is some php to change the post field on form submission to a regular date, or if there are any plans to change this in Toolset (away from Unix date) as I have seen a few questions and issues for importing data which includes dates too. Is there a reason why this is still used (unix date) please? A fix would be fantastic! Thanks... Al
Dear Alan,
How do you setup the custom field "event_start_date"?
Is it a custom date field created with Types plugin?
If it is, you can use Types shortcode to render the date field, see our document:
https://toolset.com/documentation/customizing-sites-using-php/functions/#date
And you can also use Types PHP function types_render_field() to render the field value using PHP code, see the top part of above document:
https://toolset.com/documentation/customizing-sites-using-php/functions/
To insert Types fields using PHP code, use the following function and syntax:
<?php echo(types_render_field( 'field-slug', array( 'arg1' => 'val1', 'arg2' => 'val2' ) )); ?>
Sorry Lou, I am not sure I understand your answer. Yes the date field is a field created with types as a part of a field group "date". I am not trying to display it in the cred form or front end any differently as far as toolset goes. Its more about that the DB field that the date is saved or posted to that ends up as a unix date and so WP DataTables then just takes whatever is in the field (in this case the Unix date) from the field and inserts it into the DataTable in Unix date format rather that regular date format as that is how it is written to the DB by Toolset in the first place. Are you suggesting that if I change the form field then it will we be written to the DB in a different way (regular date instead of unix) and so will then hopefully display in the DataTable in the correct manner?
Types custom date field saves value in time-stamp format(Numeric value), there isn't such a feature tor store the value as regular date format, but it is very easy to display it as regular date format, see my answer above:
https://toolset.com/forums/topic/unix-date/#post-591922
I suggest you check with the author of wpDataTables, check if it is possible to render the custom date field with Types shortcode:
https://toolset.com/documentation/customizing-sites-using-php/functions/#date
Or Types function types_render_field()
Hello Luo,
I looked at a solution at WPDataTables and noted this:
I suppose PHP doesn’t recognize the timestamp as date, so maybe you could add something like DATE_FORMAT(your_field, ‘%d.%m.%Y’) to your SELECT statement?
What I have in the DataTable to SELECT the date is:
SELECT posts_event.post_title AS event_post_title,
event_meta_wpcf_event_start_date_tbl.meta_value AS event_meta_wpcf_event_start_date,
event_meta_wpcf_event_start_time_tbl.meta_value AS event_meta_wpcf_event_start_time,
event_meta_wpcf_event_end_dates_tbl.meta_value AS event_meta_wpcf_event_end_dates,
event_meta_wpcf_event_end_time_tbl.meta_value AS event_meta_wpcf_event_end_time
FROM wp_posts AS posts_event
INNER JOIN (SELECT event_meta_wpcf_event_start_date_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS event_meta_wpcf_event_start_date_tbl_postmeta INNER JOIN wp_posts AS event_meta_wpcf_event_start_date_tbl_posts ON event_meta_wpcf_event_start_date_tbl_postmeta.post_id = event_meta_wpcf_event_start_date_tbl_posts.ID AND event_meta_wpcf_event_start_date_tbl_posts.post_type = 'event') AS event_meta_wpcf_event_start_date_tbl
ON event_meta_wpcf_event_start_date_tbl.meta_key = 'wpcf-event-start-date' AND event_meta_wpcf_event_start_date_tbl.id = posts_event.ID
INNER JOIN (SELECT event_meta_wpcf_event_start_time_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS event_meta_wpcf_event_start_time_tbl_postmeta INNER JOIN wp_posts AS event_meta_wpcf_event_start_time_tbl_posts ON event_meta_wpcf_event_start_time_tbl_postmeta.post_id = event_meta_wpcf_event_start_time_tbl_posts.ID AND event_meta_wpcf_event_start_time_tbl_posts.post_type = 'event') AS event_meta_wpcf_event_start_time_tbl
ON event_meta_wpcf_event_start_time_tbl.meta_key = 'wpcf-event-start-time' AND event_meta_wpcf_event_start_time_tbl.id = posts_event.ID
INNER JOIN (SELECT event_meta_wpcf_event_end_dates_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS event_meta_wpcf_event_end_dates_tbl_postmeta INNER JOIN wp_posts AS event_meta_wpcf_event_end_dates_tbl_posts ON event_meta_wpcf_event_end_dates_tbl_postmeta.post_id = event_meta_wpcf_event_end_dates_tbl_posts.ID AND event_meta_wpcf_event_end_dates_tbl_posts.post_type = 'event') AS event_meta_wpcf_event_end_dates_tbl
ON event_meta_wpcf_event_end_dates_tbl.meta_key = 'wpcf-event-end-dates' AND event_meta_wpcf_event_end_dates_tbl.id = posts_event.ID
INNER JOIN (SELECT event_meta_wpcf_event_end_time_tbl_posts.ID as id, meta_value, meta_key FROM wp_postmeta AS event_meta_wpcf_event_end_time_tbl_postmeta INNER JOIN wp_posts AS event_meta_wpcf_event_end_time_tbl_posts ON event_meta_wpcf_event_end_time_tbl_postmeta.post_id = event_meta_wpcf_event_end_time_tbl_posts.ID AND event_meta_wpcf_event_end_time_tbl_posts.post_type = 'event') AS event_meta_wpcf_event_end_time_tbl
ON event_meta_wpcf_event_end_time_tbl.meta_key = 'wpcf-event-end-time' AND event_meta_wpcf_event_end_time_tbl.id = posts_event.ID
WHERE 1=1
AND posts_event.post_type = 'event'
But I am not sure where to put the "DATE_FORMAT(your_field, ‘%d.%m.%Y’)" within the above code? Any chance of you pointing a non-coder in the right direction please?
Q1) I suppose PHP doesn’t recognize the timestamp as date,
You can use PHP codes to display the time-stamp value into regular date format, see PHP document:
versteckter Link
Q2) But I am not sure where to put the "DATE_FORMAT(your_field, ‘%d.%m.%Y’)" within the above code?
You are using another plugin "wpDataTables" to display Types custom date field, so it needs custom codes, according to our support policy, it is out the range of Toolset support, we don't provide the custom codes support:
https://toolset.com/toolset-support-policy/
Q3) Any chance of you pointing a non-coder in the right direction please?
I have already provide you the suggestion in above answers:
https://toolset.com/forums/topic/unix-date/#post-592079
If you still need assistance for it, please provide a copy of your website, also point out the problem page URL and layout URL, I need to test and debug it in my localhost, thanks
https://toolset.com/faq/provide-supporters-copy-site/
Thanks for the details, I am downloading the file, will feedback if there is anything found
I tried your duplicator package in my localhost, here are what I found
1) I assume we are talking about the page:
versteckter Link
It display the table different from your screenshot:
versteckter Link
I can not see the numeric value in the table, here is the screenshot for what I see: Datatable.JPG
2) edit above page, there is a shortcode:
[wpdatatable id=1]
Which is from the plugin "wpDataTables",
versteckter Link
You are using custom SQL to query the database, as you mentioned above:
https://toolset.com/forums/topic/unix-date/#post-592258
I am not familiar with the wpDataTables plugin, so I suggest you ask help from the author of plugin "wpDataTables", but it is possible to change the time-stamp value into regular date value within mysql query, for example this thread:
https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query
For your reference.
Thanks for the help. I contacted the plugin developer and they said that it is not possible to change the format from the Unix one to appear in the table. I wish that Toolset would consider changing this to do away with this issue.
Regards
Al
