Order games on lists by first release date

[Krylan]

I have some sugesstion for new feature, which could let better utilize information about "first release" of a game. Right now, because of the fact, how this date is stored into the databe, the only situation, where it is used, is to display this field content on gameInfo page, while date itself would have a much more potential to be used.

Usage

Sometimes, I'm visiting hubs to see all games in a franchise or games made by a certain developer, but all of them are sorted in an alphabetical order. If I want to know, in which order were "Castlevania" or "Fire Emblem" games released, then I have to look through each of theirs' game pages, or search in external sources, like Wikipedia, to look for an exact release timeline. First release in date format would make that ordering possible, but also the date would help in extending search posibilities, as it could be used to filter lists (for example: "show all games released in year 1995").

Challenges

I'm aware, that it would create some sort of challenge to introduce such feaature, as "First release" info in game table of database is a string type. In this field information is somethimes filled with non-date info, because it could contain only year info (when we lack information on month and day of release) or it contains few dates with regions, where the game was released. Dates there are also provided in different formats, so it is not standarized.

This information on different regions (or not full data) could be important, I guess (or I'm wrong, what do you think?), so converting this column to a date field is not an option, even if it would optimize the table and provide facilitation in operating on this data.

Idea

In this case, I thought about a solution on that, to bring a game release date into date type field: Create another column of date type in game table and store there only the very first release date. In case of unknown date or not detailed info (only year, but no day or month), the field would be NULL. Date(s) displayed on game info page would stay in the same format as they are right now, so next column would only add info for other uses of release date, as mentioned above (ordering and filtering lists). I also had other ideas, but I think they would be much more difficult and intrusive to introduce them into the database – it would create much hassle for using this data, too.

I wanted to ask you what do you think about it? You know the RAWeb database much better than me, how such changes would affect this ecosystem and how to carry them, so I'm just giving my suggestion for you to consider. I didn't find such "Issue" already, that's why I'm submiting a new one.

[Tsearo]

I think this is a great idea it's just that there are some issues to consider with implementing this like you said. Since the current data we have for release is just a string and the format people enter it in varies we can't really make good use of it to automatically convert it to a date (although we may be able to take the most popular format people use and convert those).

It would still require some good amount of manual effort to update the dates though but in my view the steps to start working on this would be:

1. In the database find the most common format we currently have for date strings and convert all of those to a date type.
2. Anything that doesn't fit that format could be exported beforehand and have it's value set to null
3. Update the release date field on the site to only accept a proper date and possibly add a date selector to it
4. Work with developers using the list of games/dates that need to be updated to get the remaining data updated again

With the above done then it wouldn't be too hard to add a date filter to some of the game pages to display and sort by.

There may be a better or more efficient way but these are my current thoughts on the issue.

[Sanaki]

V2 is going to update all date strings to ISO-8601 anyway, so we could just do that sooner. The plan was to have V2's display format be user-selectable, which won't be viable yet. I believe @luchaos may have some scripts prepped to auto-convert the bulk of them already.

[luchaos]

Can be done anytime. If we don't deem this relevant enough for v1 then this issue should be closed.

[MSGoodman]

In working on this I was surprised to see the release date can just be an arbitrary string. This ticket is somewhat related to that, but it's more like it's a followup feature to a more basic need to convert the game release date to an actual timestamp. If we agree, do we think it warrants its own issue? I'm unsure how we handle stuff like that here.

That being said, I favor a solution similar to the one suggested above:

1. Make a new date field on GameData for the game's release (actually I'm a bit confused on this point, we already have both Released and released_at).
2. Write some script/migration/command that converts as many Released values to the new date format in the new field as possible
3. Update everywhere in the code to look at this new field, and convert all inputs on the frontend to proper datepickers
4. Remove the old text-only field from the DB

On an only slightly related note, can we sometimes pull this data from somewhere like IGDB or something? I think it's nice to let users enter this manually as a fallback, but that API does seem free for non-commercial use.

[wescopeland]

On an only slightly related note, can we sometimes pull this data from somewhere like IGDB or something? I think it's nice to let users enter this manually as a fallback, but that API does seem free for non-commercial use.

IMO, preferably we reach a future state where we're not managing this data at all and primarily rely on third-party databases/partnerships in the ecosystem.

I think the strategy you've described for a migration plan should work well. To the best of my understanding, that is indeed the intent of the released_at field.