Resolved - Converting MySQL time for Reviews to a useable format

[Guest]

I am in the process of leaving Cube Cart and I noticed that the timestamp under the CubeCart_reviews "time" column is in a format like this: 1247684300

I though it might be seconds or milliseconds, but I have no idea.

Anyone know how to convert this?

[bsmither]

99% of the time, CubeCart uses what is called the (Epoch) Unix 'timestamp' value to record when something happened. It is the number of seconds that has elapsed since Midnight, Universal Time Coordinated, the morning of January 1, 1970 - the officially recognized time when computers knew to keep track of such things.

http://en.wikipedia.org/wiki/Unix_time

There are utilities one can load onto their browsers to provide a convenient converter.

I would not be surprised if whatever you are converting to will use unix-timestamps itself.

And seeing as how this value is 'universal', every programming language will have built-in functions to express the value in human-readable format.

[embroideryallsorts]

I am wondering how you can change this to make it a logical date for entering into the db. I want to set dates for my specials this will need to be done it db due to the number I need to enter. I can't do it in a consecutive date using xcell when the time stamp does not seem to follow a sequencial order.

[bsmither]

"I am wondering how you can change this to make it a logical date for entering into the db."

 

Change what? An existing Cubecart table column? I would strongly urge you to not change any existing column. By logical, I assume you mean human-readable.

 

 

"I want to set dates for my specials. This will need to be done [in the] db due to the number I need to enter."

 

Your sale dates are new columns? If so, then you can add new columns as type DATE and enter dates in almost any format - but YYYY-MM-DD is the most preferred.

 

 

"I can't do it in a consecutive date using [Microsoft Excel] when the time stamp does not seem to follow a sequential order."

 

The Unix timestamp is sequential. Any timestamp will be sequential. In converting to human-readable format, the resultant string of characters may or may not be sequential ("12/31/2000" comes before "5/5/2000" in strict alphanumeric order).

 

The thing about Microsoft Excel (thank you, Microsoft) is that the beginning of time starts at Midnight, Universal Time Coordinated, the morning of January 1, 1900 -- seventy years earlier, and the non-human-readable value is the count of the number of days with the time of the last day as a decimal component. So there is 2,209,161,600 seconds difference between what most of the world uses and what Microsoft Excel uses.

 

Then there is this: Excel calculates the first year as a leap year even though it isn't, and Excel for the Macintosh (non-linux based???) believes the beginning of time happened at Midnight, Universal Time Coordinated, the morning of January 1, 1904.

 

An Excel formula that should get you the Unix Timestamp is:

=(B2-DATE(1970,1,1))*86400

where B2 has the human-readable date.

 

Here is an interesting on-line converter: http://www.epochconverter.com/

[embroideryallsorts]

Thank you Brian I did manage to find the solution to this. to convert the time from epoch time to human readable time I put the epoch time in the 1st column then format the cell to date (right click format/date)   put this formula =(A1/86400)+25569 into B1 and hit enter, with a list of epoch times in the 1st column just double click the fill hand to repeat the formula for the entire list. 

To convert human readable time to epoch time do the opposite using this formula =(A1-25569)*86400