I had this at the end of another rather long thread, so figured I would start a new one because it has now become imperative that I get this to work :)
What I am trying to work with is a field in an existing MySQL db that stores the date and time as Unix Epoch time and does so in a field that is of the int data type, 10 characters, unsigned, nulls allowed, default is null and is named to_timestamp.
I can get it to display in the grid in the exact format that I require, 21 AUG 2012 by using this MySQL SELECT statement:
... and then applying time("dd MON yyyy",<value>) in Display Settings > Display format to the date_completed field alias.
Perfect! Accepts my conditional formatting (text and background coloring) exactly as required :)
BUT ....
Now try to enter or change the date and update the table. This is where things break down and quit functioning.
One would (well, me at least :) ) expect that all that needs to be done is to enter the A5 xBasic time_to_unixTimeStamp(<value>) function in Display Settings > Display unformat as the following is the description of this function and exactly what needs to be done:
Furthermore, the hint for Display unformat says exactly what I need to happen.
But it doesn't. In fact, nothing does. If there is an existing date, changing it and submitting does nothing; stays the same. No errors, just refreshes the existing date.
If one adds a date to where there was none, it is saved as 31 DEC 1969 with a 0 stored in the to_timestamp field.
In the other thread, Sparticuz posted this code:
If I knew where to put it and how to use my fields, that would be fantastic! :) But shouldn't the built-in 'Display Unformat' be able to do the job? Isn't that exactly why it is there? I just know that I am missing something easy here :)
If anyone can shed some light on this, it will be much appreciated as I have exhausted just about everything I can possibly think of!
What I am trying to work with is a field in an existing MySQL db that stores the date and time as Unix Epoch time and does so in a field that is of the int data type, 10 characters, unsigned, nulls allowed, default is null and is named to_timestamp.
I can get it to display in the grid in the exact format that I require, 21 AUG 2012 by using this MySQL SELECT statement:
Code:
SELECT from_unixtime(to_timestamp) AS date_completed
Perfect! Accepts my conditional formatting (text and background coloring) exactly as required :)
BUT ....
Now try to enter or change the date and update the table. This is where things break down and quit functioning.
One would (well, me at least :) ) expect that all that needs to be done is to enter the A5 xBasic time_to_unixTimeStamp(<value>) function in Display Settings > Display unformat as the following is the description of this function and exactly what needs to be done:
"time_to_unixTimeStamp() Function - Converts a time value to a UNIX time stamp - number of seconds since 1-Jan-1970."
But it doesn't. In fact, nothing does. If there is an existing date, changing it and submitting does nothing; stays the same. No errors, just refreshes the existing date.
If one adds a date to where there was none, it is saved as 31 DEC 1969 with a 0 stored in the to_timestamp field.
In the other thread, Sparticuz posted this code:
Code:
dim unix as n = 1344973036
?unixtimestamp_to_time(unix)
= 08/14/2012 03:37:16 00 pm
dim time as t = {08/14/2012 03:37:16}
?time_to_unixtimestamp(time)
= 1344915436
If anyone can shed some light on this, it will be much appreciated as I have exhausted just about everything I can possibly think of!
Comment