Is it possible to make ASC or DESC a text field from sql?
I have a field called ‘comment’ and in there each update on users are recorded as ‘date - decision taken’.
Is it possible somehow to retrieve the info based on what date is written in the text field?
I mean… 23rd jul 2019 - changed password to appear after 25th jul 2019 - user warned or something like that in a table… Ive tried using ASC or DESC on the field itself but it always takes the first character in the text as reference and it doesn’t always show properly
Dates should be stored using a DATE data type. This will allow them to be compared or sorted, will take the least amount of storage, will result in the fastest queries, and will allow you to use the built-in datetime functions to operate on the values. You can format a DATE data type any way you want when you display it on a web page.
If you have existing data, you can convert it into a DATE data type by -
- Backup your data, just in case.
- Add a new DATE type column to your table.
- Using STR_TO_DATE(), in a single UPDATE query, set the new DATE type column from the existing dates.
- After you have confirmed that the new DATE type column has been populated correctly and you have modified any queries and code to operate the new DATE type column, delete the previous column holding the dates.
Is it possible to order by a field asc/desc by the last time it was updated?
If its set lets say as desc to show first if that field was updated 1sec, 5 sec 10sec ago?
I am trying to do so but it doesn’t do the right thing.
I get first the ones from 2018 data into them
I believe it takes on what is written in the field since its ‘text’
After you convert your values to a DATE or DATETIME data type (see the reply in your other thread) you can use TIMESTAMPDIFF() in a query to either SELECT or ORDER BY the difference in seconds between the stored DATE or DATETIME and any other DATE or DATETIME value, such as the current datetime. If all you are really doing is ordering the values in a descending order, you can directly do so after converting your values to a DATE or DATETIME data type.
But in that field is only text. That says what action has been taken on a particular date. Nothing more. How to convert a simple text to date? I dont understand
In that field its not just only dates, there are also full sentences 
that tells me you have a bad table design attempting to be a do everything table
Hmm, and i cant order them by the last update time?
I mean if i update some user that field also get updated and lets say i update some users who’s comment box hasnt be updated since 2016, that user will appear first in the table since his comment box got changed recently.
And so on… The recent updates get to show first
I dont know how to explain it otherwise… I dont even know if order by in mysql supports this
The date and comment should be stored in separate columns.
That is obvious now… But without this i cant do anything?
Anything is possible, but may not be desirable. By having a date format that is not sort-able and as part of other text, a query cannot use an index. Every row of data would need to be read by the database, with the date converted to a use-able format, to find and order information. Once you have a real amount of data, every query will take a long time to execute.
If you have a bunch of existing data, it is possible to construct a query that will split the data, convert the dates, and set two new columns with the data.