Template Variables of Type 'Date'

24 Posts

tester3 Reply #1, 8 years, 11 months ago

Hello.

I have tried to import template variables to the MODX site using ImportX and it appeared that T.V. of 'Date' type are stored in the MySQL database not as other date-typed parameters (e.g. 'publishedon' and some others).

The 'publishedon' parameter is stored as 20-byted int, int(20), but T.V. of date type is stored as mediumtext in MySQL (it is a text of maximum 16 MiB size).

i want to store dates as timestamps, as 'publishedon' parameter does. for me it is much easier to work with timestamp rather than with "ABCDEFG JOHN DOE WAS HERE".

How can i make a T.V. to be stored in MySQL database as timestamp (integer) and appear to the end user (manager of the site) as Date (with convenient calendar GUI) ? Is it possible?

Thanks!

This question has been answered by BobRay. See the first response.

24,544 Posts

BobRay Reply #2, 8 years, 11 months ago

MODX stores all dates as timestamps, including date TVS, but it converts them to human-readable dates when they are retrieved. It's possible that ImportX doesn't do this properly, but I doubt it. You'd have to look in the modx_site_tmplvar_contentvalues table to be sure.

Assuming that they're stored as timestamps, if you are getting them in code, you can use $tv->getValue($resourceId) to get the raw timestamp.

If you're getting them with TV tags, you can format them however you like with something like this:

[[*TV_name:strtotime:strftime=`%a %b %e, %Y`]]

Some more ideas and explanations here: https://bobsguides.com/blog.html/2013/09/18/displaying-modx-date-fields/

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

24 Posts

tester3 Reply #3, 8 years, 11 months ago

Hello, Bob.

Quote from: BobRay at Oct 05, 2017, 12:11 AM

You'd have to look in the modx_site_tmplvar_contentvalues table to be sure.

That is the exact table i was looking at when i quoted the variable types. And yes, my eyes are telling me truth, i see "mediumtext" as a data type of my T.V. (not an "int") which stores date. Seems that something is working not as we could think of it.

Proof:

1. MySQL Screenshot:

2. MODX Screenshot:

[ed. note: tester3 last edited this post 8 years, 11 months ago.]

24,544 Posts

BobRay Reply #4, 8 years, 11 months ago

I stand corrected. Looking at the docs, it doesn't appear that ImportX knows anything about TV types, but I haven't looked at the code.

I assume that you created the TVs themselves before importing.

What do you see when you use a TV tag to display them?

What's in the 'type' field in the modx_site_tmplvars table?

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

24 Posts

tester3 Reply #5, 8 years, 11 months ago

Quote from: BobRay at Oct 05, 2017, 05:26 PM

I assume that you created the TVs themselves before importing.

Yes. I created them before using ImportX.

Quote from: BobRay at Oct 05, 2017, 05:26 PM

What do you see when you use a TV tag to display them?

[DEBUG_START]
[[*spoffer_date_start]]
[DEBUG_END]

is rendered in the content as:

[DEBUG_START] 2017-01-10 09:32:00 [DEBUG_END]

Quote from: BobRay at Oct 05, 2017, 05:26 PM

What's in the 'type' field in the modx_site_tmplvars table?

for the TV with id=36 ('spoffer_date_start')
'type' is 'date'.

discuss.answer

24,544 Posts

BobRay Reply #6, 8 years, 11 months ago

It appears that I'm partially full of crap. wink

MODX resource date fields are all stored as timestamps, but Date TVs are stored as human-readable date/time strings. So ImportX is handling things correctly and the display is as expected.

On the Output Options tab of the TV, you can set the format they will be displayed in (I believe the format is that of strftime()). Make sure the output type is set to date as well as the input type.

You can also do this to display them in a different format on a particular page:

[[*TV_name:strtotime:strftime=`%a %b %e, %Y`]]

If you need to deal with them as timestamps in code, use strtotime() to convert them to timestamps.

If you really need to have them as timestamps in the DB, you could write a utility snippet that would convert the 'type' to 'text' (IIRC, 'integer' it not a valid type in the DB), and the value to a timestamp, but this could end up changing TVs attached to an Extra that won't handle them correctly after the change.

Did I help you? Buy me a beer
Get my Book: MODX:The Official Guide
MODX info for everyone: http://bobsguides.com/modx.html
My MODX Extras
Bob's Guides is now hosted at A2 MODX Hosting

24 Posts

tester3 Reply #7, 8 years, 11 months ago

Thanks for help.

I think, output type of a T.V. changes only the post-processing (after read from database) of the T.V. I hope that in future versions of MODX there will be some way to change the data type of T.V. which is used to store it in the database (to make Date-typed T.V.s be stored as integer timestamps). [ed. note: tester3 last edited this post 8 years, 11 months ago.]

Answered Template Variables of Type 'Date'