Store TV Date in Unixtime [SOLVED]

Hi -

I would like to store a date defined in a TV as a unixtimestamp. Right now, I used the unixtimestamp widget with a "Date" input type and it still stores the value in the database as a String (ie., ’02-07-2009 18:00:00’). Since I am retrieving the value directly from the database (using in Flash), I need to get this value as a unix timestamp. I thought the unixtimestamp would do this...

TV date types use a Javascript datepicker, which produces the string date that is saved to the database. The unixtime output widget converts that to a Unix timestamp on output. In your case, you’re never using the output widget since you get the value directly from the database yourself. I’m afraid your code that gets the value will have to convert it. Perhaps you could use a query using the unix_timestamp() function like this to do the conversion for you:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp

unix_timestamp returns ’0’ because the string isn’t formatted properly. In order for this to work the date has to be stored as such: 2009-07-18 18:00:00

In MODx it is stored this way: 18-07-2009 18:00:00

I now have to figure out how to get this string formatted properly. Can you possibly use @EVAL to get a unix timestamp stored in the database? I would really like to take a date from the user in a human format, then store it in the database as a unix timestamp.

Well, then I’d advise a plugin using the OnDocFormSave event. Have the plugin code convert the POST tvname value, then store it in a custom table, along with the document ID.

You could use the OnBeforeDocFormSave, and just convert the value, and leave it for the save process to continue to store the unixtime value in the normal way in the site_tmplvar_contentvalues table. But in this case you would need to keep in mind that this particular TV date is not stored in the normal format, and you would not be able to use the Date output widget with it.

Here’s the code MODx uses for pub_date and unpub_date, which use the same date/time picker:

Ok, thanks very much. I may just do this in my PHP class and format in Flash so I can still use this within MODx.

Further looking into MySQL date formatting functions, I see the str_to_date() function, which uses a lot of %x characters to format a given string into a proper MySQL formatted date. I would imagine you could craft a query using this and the unix_timestamp function to get a timestamp from an existing TV date. It’s giving me a headache just to think about it

http://bytes.com/topic/mysql/answers/547645-convert-string-date

Yep, that is exactly right. Thanks for pointing me in that direction. Here is the resulting query that gets me the proper date format from the TV:

SELECT a.id, a.parent, a.pagetitle, a.introtext, a.alias, b.tmplvarid, b.contentid, UNIX_TIMESTAMP(str_to_date(b.value,’%d-%m-%Y %T’)) as time
FROM site_content a, site_tmplvar_contentvalues b
WHERE b.tmplvarid =7
and b.contentid = 9
AND a.id = b.contentid

ouch...ouch...ouch my head!

No, but really, that’s great. Could you edit the subject of the post and add [SOLVED]? It helps others with the same or similar questions know that you have the answer.

I remember reading somewhere many years ago to always try to let the sql engine do conversions and calculations whenever possible, especially with dates, and let the scripting engine stick to dynamic content; sort of a division of labor. I suppose it’s not really of that much benefit if both are on the same server, but it’s nevertheless not a bad habit to get into.

Quote from: tpelligrino at Jun 25, 2009, 04:05 PM

In MODx it is stored this way: 18-07-2009 18:00:00

I have no idea, why this would be allowed. It is so counter the typical thinking of the devs.

Why not use a simple php created unix timestamp in the table?

All of the functions are already in modx and very simple snippets can be written using the modx functions to do the translating.

Using the above method essentially doubles the storage size in the database from the int(11) used in other areas.

I am glad you figured it out though.

It’s because that’s how the date picker javascript widget generates the date. Also, a date TV can accept a user-specified date, which may take any form. It’s stored as-entered for greater user flexibility. That means we have to take extra care on using it; that’s the price for the flexibility.

The pub_date and unpub_date use the same date picker, but in that case the POST values are converted to timestamps before being saved. And people complain about that sometimes.