Hello again...

If you are reading this, my most recent project must be up and running. Hurray! After many trials and many errors this blog engine is ready for content. It may be a bad move security-wise, but I think its worth the risk to share some of the workings of the system with you. On the back end the blog uses PHP and MYSQL. On the front end it's running JS, JQuery, uses AJAX to retrieve post data, and uses bootstrap for styling.

Overall its a pretty basic setup. The most unique feature is probably the extra date to sort by. "Date of Work:" is the date that I started working on what ever it is I may be writing about. You can see in the lower left that I started working on this blog engine on 2/9/2017 and once I've posted a bit more it will be worth mentioning that you can sort posts by this date to get a chronological view.

Timezones

Time... This brings me to the bane of the project... timezones. My first thought was that it should be able to display and receive timestamp data from any timezone. My first mistake was thinking that this should be simple to do. There are a few layers between the form and the database but as long as the timestamps are kept as UTC+0000 unix timestamps, the data only ever needs to be altered for display. "I can do that!" (facepalm).

What I didn't realize until much later was that each layer was already trying to convert from UTC to it's own timezone for me. First, MYSQL may or may not convert timestamps to the server's timezone upon selection depending on server settings. Second, the JS date object behaves differently depending on what format you pass a date to it in. Finally, PHP's strtotime() function also seems to convert in some instances. So here is the model I wound up with and how I got there.

---

The Model - Form to Database

--1-> [HTML form] --2-> [JS] --3-> [PHP Server] --4-> [MYSQL DB]

1. The form takes a datetime as a string
2. The form input passes the date in ISO 8601 format to the JS script
3. JS initializes a date object from the string its given and then uses Date.getTime() to send the UTC+0000 timestamp to the server.
   This initialization was a major source of problems for me so to make sure JS behaved predictably I altered the method of initialization with this function.
   

   function timeStampFromFormValue(dateString){
   	var year = Number(dateString.substr(0,4));
   	var month = Number(dateString.substr(5,2));
   	var day = Number(dateString.substr(8,2));
   	var hour = Number(dateString.substr(11,2));
   	var minute = Number(dateString.substr(14,2));
   	var second = Number(dateString.substr(17,2));
   
   	return new Date(year,month-1,day,hour,minute,second,0).getTime();
   }
   

   This takes 'dateString' as the output format returned by one of these.

   <input type="datetime-local" />

4. The server receives the timestamp as a long integer (unix timestamp) and inserts it into the appropriate query.

---

The Model - Database to Form

[HTML form] <-3-- [JS] <-2-- [PHP Server] <-1-- [MYSQL DB]

1. Upon receiving an AJAX request for blog post data, the server runs a query to select rows of data from the database.
   Here I ran into trouble when the select query would retrieve TIMESTAMP data types as a string and automatically convert to the server's timezone. I'm sure there is a more correct way to deal with this problem but I found it worked to use the INT datatype instead of the TIMESTAMP or DATETIME types when storing it with MYSQL. This comes at the cost of human readability on the back end but it assures the system isn't going to alter your data before it gets to the client. (If you know a better solution, by all means please let me know)
2. Once the server has the data, it encodes it as JSON and sends it back to the front end where JS is waiting to receive it.
3. JS then initializes Date objects with the appropriate fields and uses them to output strings to the page for display and to form fields as default values.

---

A Few Last Words

I hope this has been useful. I'll list links to some of the pages I referenced for the project below. In the interest of improvement, let me know if you have any alternate(better?) solutions to this or resources on the subject and I'll see about adding links to them here as well.

Thanks for reading.

---

JS Date object documentation

• https://www.w3schools.com/jsref/jsref_obj_date.asp
• https://www.w3schools.com/js/js_date_methods.asp
• https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date

HTML input datetime-local documentation and references

• https://www.w3schools.com/jsref/prop_datetime-local_value.asp
• http://stackoverflow.com/questions/10854874/input-type-datetime-value-format

MYSQL TIMESTAMP and DATETIME documentation

• https://dev.mysql.com/doc/refman/5.5/en/datetime.html

PHP strtotime documentation

• http://php.net/manual/en/function.strtotime.php
• https://www.w3schools.com/php/func_date_strtotime.asp