Standardize date/time fields in the database

[brunnopleffken]

Addictive Community currently uses UNIX timestamp in INT-type fields to store dates and times. This has a number of implications, such as the timestamp date range (2038 in 32-bit operating systems), as well as the inability to use SQL to perform date and time operations.

My intention is: in version 0.13 to standardize all date and time fields to SQL timestamps (YYYY-MM-DD HH:MM:SS), as well as the name of the fields. The suffix of the fields will be [verb]_at instead of _date as currently defined. To better organize the columns, we'll always place date fields last (the same way Ruby on Rails does with t.timestamp).

[halojoy]

php date() format is Y-m-d H:i:s
This is actually what I use in my applications.
Did not know it was sort of a SQL timestamp standard.
What should I use for name of fields? When CREATE TABLE?

[brunnopleffken]

Yes, Y-m-d H:i:s is the same pattern that DATETIME and TIMESTAMP fields use. The difference is that TIMESTAMP stores years up to 2038, while DATETIME up to 9999 (great for leaving information for future archaeologists).

About the name of fields, actually, there's no convention. Ruby on Rails uses "created_at" and "updated_at". Some SQL Server applications I've seen uses "created_on", "started_on", "edited_on", etc. I think the best is to create your own project standard.

Just be aware that "at" may also indicate a location. So if there's any kind of geolocation-oriented feature in your application, I think it's worth using "_on" or "_date" as a suffix to avoid being ambiguous.