Add database support for DateTime field

[hakusaro]

Currently, if I recall what @bladecoding said, we don't support the DateTime type in the database system. Per the request here, we should add support for it.

[AxisKriel]

Why aren't we using the DATETIME type for dates in the tshock db? Both SQLite and MySQL support the type, I see no reason not to use it.

[hakusaro]

I'm not sure there's a good reason as to why we aren't using it, but even if we add support, there's a big difference between making it so that the current tables use it versus future ones.

[AxisKriel]

Yeah, my question was more directed at why didn't you use it when first creating the database class, if you still happen to remember why. If we do the change now, we'll be facing compatibility issues between databases.

[hakusaro]

@Enerdy the database was implemented by not me, so I can't really answer why. This issue more adds the support for the type, and doesn't change the existing structure.

[mCampy]

I know for me I usually just stored UNIX epoch time stamps in INTEGER fields and used SQL functions to do my queries.

I think that SQLite does not formally support any date/time representations and instead forces the workload onto the user in order to store their preferred representation. I guess I am curious what the actual use of this would be with that in mind, as it seems it would only affect MySQL.

[hakusaro]

@mCampy I store epochs in int fields too. SQLIte does support DateTime, though. We just don't use it.

[mCampy]

According to this: http://www.sqlite.org/datatype3.html they don't support DateTime but rather treat it as numeric, also, in regards to whats supported, you can have field types called "APPLE" and it will treat it as numeric. Ultimately it resides on the developer implementing the sql query to insert and retrieve the representation that best fits their application.

[hakusaro]

@mCampy there are still functions available for dealing with it, though: http://www.sqlite.org/lang_datefunc.html

[hakusaro]

Though, you pose an interesting problem. If someone is using DateTime from our implementation, then we move the work onto them.

This database system is kinda archaic.

[mCampy]

You sir are correct. However, those are a part of the database and not TShock, right? strtime(), date(), datetime() are all functions provided by the connector, just like now() is a MySQL feature.

[hakusaro]

Yes, they're part of the database @mCampy. The interesting thing to note here is that this might just add more code rather than less, by using DateTime over epochs. Anyone supporting DateTime has to deal with two different methods of accessing things, where as using epochs its clear: the format is unix epoch, which must always be converted into a useful object after use.

[hakusaro]

This is super old, closing because Orion will get a new database, and there's been zero activity on this since the issue was introduced as far as code goes, and I don't think this is a super big deal.