Sorting Time Values
I have been trying and trying to properly sort records through a mySQL
query. I have hour, minute and AM/PM values stored separately in a
database as varchars. I'm trying to concat those, cast them to a time
value and sort them. I have found that it sorts some properly but other
it does not.
The following query:
CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime
ORDER BY myTime
Sorts some results like this:
10:30 PM
8:30 PM
9:30 PM
But other are perfect:
10:00 AM
11:00 AM
12:00 PM
1:00 PM
2:00 PM
3:00 PM
4:00 PM
5:00 PM
These exact results we part of the same recordset. Can anyone help me
with this?
View Complete Forum Thread with Replies
Related Forum Messages:
PHP/MySQL Sorting By Date & Time (using Non Military Time)
This is probably a simple issue but I have searched online and can't find an answer. I am using PHP/MySQL and I guess the most intuitive would be to have three select boxes containing HOUR / MINUTE / AM,PM option. Then store the time into mysql using there functions so I can output the data sorting them all by date and time. All the examples I have seen are for military time only but I'm sure there has got to be a simple mysql function or php function that converts non military to military and a formatting function to display with the AM / PM. But I have not found it.
View Replies !
Sorting Column With Values A-Z Before AA-ZZ
I need to sort based on a column that has values as follows: row ---- A B C D AA BB CC DD etc..... i need the data to be sorted and displayed as A-Z then AA-BB. I saw this thread: http://forums.mysql.com/read.php?100,69968,70410#msg-70410 is there something similar, that would allow me to sort this way.
View Replies !
Displaying Time Values
When I link a MySQL table containing a TIME column to Microsoft Access the value does not display. If I edit the value it updates correctly in mysql but if I refresh the Access datasheet view the value disappears and the column shows as blank. Can anyone tell me how to display time values in Access. I am using MySQL ODBC driver 3.51, MySQL 4, Access 2002
View Replies !
Elapsed Time Between 2 DateTime Values
MySQL Version 4.0.20 on a Linux server. How does one get the elapsed time between (2) DateTime values? I need the answer to the nearest minute. Is upgrading to Ver 5 with its more robust date/time functions the only solution? You can directly subtract 2 DateTime values and a long integer results. What is that number?
View Replies !
Working Wit Date/time Values
I'm using the following query: Quote: SELECT * FROM IT_event_cal WHERE '$date' BETWEEN start_date AND stop_date Where start and stop_date are of type date/time (0000-00-00 00:00:00). For this particular query, I'd like to match a particular date (0000-00-00), and any time. How can I make the time part a wild-card value?
View Replies !
Truncated Time Values Using TIMEDIFF With ORDER BY Query
I'm using the following query: SELECT glider, timestamp, TIMEDIFF(timestamp, UTC_TIMESTAMP()) AS last_contact FROM surfacings INNER JOIN (SELECT MAX(timestamp) AS most_recent FROM surfacings GROUP BY glider) AS tmp WHERE surfacings.timestamp = tmp.most_recent; to calculate the amount of time that has elapsed since the last inserted timestamp for each glider. Everything works fine: ....
View Replies !
Date/Time As A Default Values For A Table Column
I am new to mySQL, so this question might be simple.I want to add a default value to a column that is the current date/time. I am using the mySQL Administrator and will not allow me to use a function like CURRENT_TIMESTAMP() or NOW() as a default value. I used to do this with other databases (I always add a column to all of my tables called InsertDateTime and UpdateDateTime. It helps to track down data entry problems)
View Replies !
Converting Time Date Values To An Absolute Number For Data Retrieval
We have a medical DB which stores several medical variables from real cases. We use this data to build display trending information on a visualization display for doctors. Pulling and rendering graphically ALL the data takes time and processor that may not be available in a real world situation. What we want to do is to pull data at specific intervals of time based on the time length a doc wants to view. For example: if we have 2500 records all of which have a time stamp and each record is for an interval of 15 seconds how can I query the DB such that I can pull every data point at an interval of 75 seconds apart from the beginning time and display it? I was thinking if I could convert the timestamps to an absolute numeric value (assuming MySQL starts it's times at some point in the past) then I could figure out the query from there.
View Replies !
Compare Time Posted With Current Time Not Working
May I know how to get records with interval of 1 hour in database where the posting time is in this format '2008-05-15 00:10:40' I tried with this : $query="SELECT date,name FROM message WHERE (timediff(date,NOW())<=CRUDATE() CURTIME())"; But is not working. May I know what is the correct way of doing it?
View Replies !
Eliminating Values From A List / Finding Non-existing Values
I have a list of thousands of values, some of which exist in the database, and some of which do not. I'd rather not loop through the list, SELECTing each item to test its existence, and was wondering if there's a more elegant way to do this using an SQL statement. For example: Database: 1 3 5 6 List: 1 2 3 4 5 I'd like MySQL to return to me the following, a list of the non-extant items I passed: Result: 2 4
View Replies !
Minutes + TIME = TIME Function
I need some way to convert minutes (e.g. 80 mins) to SQL TIME (e.g. 01:20:00) in order to add it to another TIME var. Is there any way to do this? I really dont want to convert my whole db. Basically, I want to do: e.g. 80 + 10:00:00 = 11:20:00.
View Replies !
How To Retrieve Mean Time Between Time Stamps
I have a table that stores failure information of external application. The time stamp of every failure event is stored. Now I'd like to make some system performance & reliability statistics calculations based on time stamps in certain recordset; mean time between failure MTBF, down time DT, etc. Example: 2007-09-27 12:23:52 2007-09-27 08:50:23 2007-09-26 23:31:34 2007-09-26 21:45:33 Here the times between are (hours:mins): 3:33, 9:18, 1:46 and MTBF=4:52 Any effective possibility to make this example within a single query - or a procedure is needed?
View Replies !
Time Format Military Time
I know the time format it HH:MM:SS in mysql but can I input military time? reason I can is that I want to later extract that information for calculations and military time is easier to work with.
View Replies !
Date-time Overlaps Another Time
had a time question in mysql... i have a start and end time for a meeting table and i am comparing whether a new meeting conflicts with another meeting in same room on the same date. i was wondering if i could check whether the 'date-time duration' in anyway overlaps another meetings date-time duration. visualized below... ............|____compareToThisMeeting__| .....|_________meetingNewOverlaps__________| i was doing something dumb before i realized i needed soemthing more complex (checking whether the start time of new meeting was 'BETWEEN' the start and end times of a meeting and whether the end time time is as well but i forgot the duration/middle value hehe). (using asp) and just returning booleans with a mySQL SELECT statement is what i am looking for like... //within a loop in asp for meetings on same date/room "SELECT '"+ year+"-"+month +"-"+day +" " + startHourString +":" + startMinuteString +":00''"+ " BETWEEN '"+ thisMeetingStart +"' AND '"+ thisMeetingEnd +"'"; (this doesnt cover the 'middle' overlap areas though)
View Replies !
Sorting By Value
How can I fix this? Using the following to select and sort: $query_land = "SELECT id, type, estate, parcel, asking, zoning, `size`, selling FROM sold2005 WHERE type = 'land' ORDER BY estate ASC, selling ASC";
View Replies !
Not Sorting
some how my query is not sorting ASC or DESC, what am doing wrong? Code: SELECT DATE_FORMAT(jdate, '%a. %M-%d-%Y') as jdate, jid, jtitle FROM hrjobs WHERE jstatus ='on' ORDER BY jdate ASC
View Replies !
Sorting Twice
I want to sort my query by date then by name. Right now I'm using: SELECT * FROM `race_calendar` order by date asc How can I add the second sort?
View Replies !
Sorting By A Particular Value
I need to sort a query's results from a database and I only want a particular value to be first. So if any of the query results equal "text" (the string) I want them to be the first values returned.
View Replies !
Multiple Sorting ?
In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well.
View Replies !
Multiple Sorting
I'm pretty new to SQL and databases, and i've come up with a problem to sort my data. I'm trying to do a basic forum thing. In my table i've got a column subject, a column content and a date. I'd like to sort the data first by date, then by topic, and then by date gain, if you see what i mean. I would like to group entries that have the same subject together, sort them by date, and inside each group sort by date as well. Am i making sense ? Is it possible to do that or am i thinking the whole thing in a wrong way?
View Replies !
Sorting And Merging
I have a table in the following style : Artnr Detail ----- ------ 110391 grey and 100391 blue color 100392 Green 100393 black 100393 monitor .... So sometimes one article has more details. My problem is, i have to get rid of that and sort the table in this way : Artnr Detail ----- ------ 110391 grey and blue color 100392 Green 100393 black monitor Is there a way to manage this with mysql or does it need php scripting.
View Replies !
Sorting Numbers
I got a list of movies and would like to select alle the movies starting with a numer 0-10 and another symbol (., #, !, etc.) but I don't know how to get them all in the same query. How do I do that? My query should look like this, but I don't know what to put instead of the ?? "SELECT * FROM movies WHERE title = ?? ORDER BY created"
View Replies !
Sorting A Table
I have a table with 20 rows and 8 columns,i want to sort the table when i click the column.can any one tell me what is the query for that?
View Replies !
Alphanumeric Sorting
I need to sort a column that may contain any combination of letters, spaces, or numbers ... including all letters or all numbers, but will never be empty. "ORDER BY code" doesn't cut it for numbers because "10" would be listed before "2". "ORDER BY (code+0)" works for most options unless the "code" is all letters or contains something like "1A" and "1B" where "1B" would come before "1A".
View Replies !
Problem In Sorting
I have a table 'PHONE' with column 'price'. I declared it as varchar type. When I select the table sorted by price. It only follows the order as string. For example, when there are price 20, 100, 35. It displays the result as 100, 20, 35, which is incorrect. I want it to show as 20, 35, 100. Does someone know a easy to correct it? (I prefer not to change the data type in the table)
View Replies !
Sorting Date
Code: SELECT date_format(date, '%b %D, %Y')as Date, time_format(time, '%l: %i %p')as Time, description, prerequisite, availability, location FROM course WHERE 'date' >= current_date ORDER BY date DESC When this is displayed it shows April 14th, April 1st, then April 7th. I imagine this has something to do with the formats?
View Replies !
Date And Sorting
I made a script that loads info from a Excel sheet into our mySQL database, however, I am running into a problem in the new year. From Excel it saves the date as 01/01/05, and can not be saved any other way from the software it is generated from. So, I made the field it is inserted in the dB a VARCHAR field. Now when I try to sort by date in ASC or DESC, it pulls all of the dates like this: 01/01/04 01/02/04 01/04/04 01/04/05 01/05/04 01/05/04 01/05/05
View Replies !
Selecting And Sorting
I have a query: mysql_query("SELECT * FROM table WHERE code IN ('G1','D36','M17')") I want to be able to sort the results in the same order as they are specified in the above, i.e G1-D36-M17. However, since I entered them in the database in the order D36-G1-M17, that is the order in which they are displayed by default. Is there any way I can specify which rows I want to pull from the database AND the order in which I want them displayed, at the same time? If not, is there any other way I can get them to sort in this order without adding an index to the database?
View Replies !
Sorting In Order
I would like to know if it is possible to order the results by the quotient of to numbers.For exampel: $sarray=mysql_query("SELECT * FROM table ORDER BY (rating/votes) DESC"); anyone have a clue how to get it to work?
View Replies !
Sorting By Last Inserted
Is there a way I can sort the rows in a table by last inserted if i don't have an autoincrement id or timestamp. Currently when I do a query on the table its returning the older rows first. I'm using this table for a log, so I took off the autoincrement id since this log is going to be ongoing and will be truncated from time to time to keep it small. When I say truncated, I don't mean the actual TRUNCATE term in mysql. I mean shortened.
View Replies !
Sorting By Quarter
Got a question about sorting into quarters. I have a news app that displays the quarters current news. I then want to show the previous quarters news in another part of the site (kinda like an archive). I have a date field that I can pull from to sort with just not sure where to start.
View Replies !
Reverse Sorting
Is there anyway to sort in a different order in mysql than it usually does. Here is an example. m100 m20 Mysql will order them in that order but the problem is that m20 should come before m100.
View Replies !
Sorting Records
I have a table that holds the monthly returns for different portfolios. The fields in that table are: PORTFOLIOID, DATE and RETURN. A typical record would be 1----31 Jan 2003----2.3%. Another record could be 1----28 Feb 2003----1.5% Another record could be 32----31 July 2002---3.5% etc I need a query that gives me the top 10 portfolios ID order by the compounded return between two dates (these dates should be variants) so that I can ask for the 10 best performing portfolios between say March 2001 and Jun 2004
View Replies !
Sorting Rows
i have two tables, posts and comments. posts has an id column, and comments has a postid column which refers to the post's id. both have timestamp columns, which are unix style timestamps(time() from php). i want to select * from posts and have that ordered by the highest (most recent) timestamp from either itself or it's comment(s?). basicly, i want the comments to bump the posts. i've been messing arround with left joins and max() with not much luck. if possible, i'd like any extraneous columns needed (if any) for sorting not returned. a spanglish of mysql: select * from posts order by (fuzzy part: most recent timestamp of comment with corresponding postid or , if there are no comments, it's own timestamp) i think i repeated my self, but i'm trying to get the point across. it's post bumping code for a forum that i'm writing. currently i do it in a god awful way using php and lots of loops, and i was hoping there was a faster way to do it in sql.
View Replies !
Two Types Of Sorting
I have a database which has these colums: id, weith and date f. I want to select some of my datas but in this way. If the diffrecence between today and the date that is stored into database is less then 30 days, then it must sort first by weith and after that by date. If the diffrences is more that 30 days, then it must sort first by date and after that by weith.
View Replies !
Sorting Is Wrong
I have some products in the database and trying to sort them ascend. by their prices. But for example, the price $14.00 comes before than $8.00. How can I correct the problem? As I understood, mysql only checks for the first digit. Not the whole.
View Replies !
Sorting Dates
I have a database which I am using to store written articles of text or a Website. I want to be able to offer the user the ability to search for articles in any given month, let's say July 2006 or August 2005. At present my tables consist of fields for - day - month and - year ...each of data type int. I then run a basic php function to find the number of days since 1/1/1970, given these 3 variables. It is this figure which I sort on. Now I know that this is not the best method, so which data type should I be using to make my life easier and my code more efficient.
View Replies !
Sorting (ORDER BY)
in MySQL are rows with these values: 995 25120 991 99 1132 86 I would like to sort them. But when I tried to use this SELECT * FROM table WHERE something='$something2' ORDER BY numbers DESC result was this: 995 991 99 86 25120 1132 and that's absolutely wrong! Result I want is something like that: 25120 1132 995 991 99 86
View Replies !
Sorting On Two Columns At Once
I have a table with 2 columns called "lastname" and "company" that I would like to sort. Sometimes the lastname column is null, and in that case I would like to use the company column for the sort. Is this possible? E.g. this data: [lastname, company] smith, amway jones, IBM <null>, microsoft should output the rows in this order: jones microsoft smith
View Replies !
Grouping / Sorting
However the data is brought back and sorted by month and I was wondering if it's possible to bring back the data the has franid,barid in every month of the year. So right now, as you can see showing the results for the first 3 months, Month 2 has data in the second row I don't want returned and in any calcultions. The franid, barid combo has to be represented in each and every month, in this particular case between Month 1 and Month 12. 1234, try, 7894.89, 1 1456, tre, 7842.78, 1 3526, gft, 5690.00, 1 1234, try, 6794.89, 2 6789, ght, 7842.78, 2 3526, gft, 5340.00, 2 1234, try, 2394.89, 3 1456, tre, 4542.78, 3 3526, gft, 8990.00, 3
View Replies !
Sorting By Timestamp
I need to sort and echo out the 5 most recently either Added or Modified entries in my table, and am a bit confused as to how to do that. I know the statement goes something like: SELECT * FROM table WHERE timestamp > .... LIMIT 5 Well, I think I know that it goes like that! *scratches head* Also, I know this is not a PHP board, but would you know how I would store and echo those results out into different variables as well?
View Replies !
DAYOFWEEK Sorting
This is the scenario. I have a radio schedule calendar that is not based on specific dates, but by day of the week. This could either be a numeric value of 0-6 (1-7) to represent the days or it could be varchar fields with the full days spelled out like Sunday, Monday, etc. What I need to do is sort this so if today is Sunday, then Sunday is at the top of the list followed by the rest of the weekdays in correct order. If today was Thursday then it will automatically have Thursday at the top of the list followed by Friday, Saturday, and so on. Is this possible and could anyone provide a solution for such automated sorting?
View Replies !
Sorting On Date
I want to show a list that is ordered by birhdays, where the person who have birthday soonest will be at the top of the list. Can anyone please give me the syntax for this request.
View Replies !
Sorting Addresses
I am working with MySQL to output a list of addresses. What I'm trying to do is sort the output by the address, but instead of sorting by the leading numeric values, I want to first use the alpha values and then the leading numeric values. For example, say I have the following records in a table called USERS in the column Address1: 100 Fifth Avenue 48 Fifth Avenue 19 Seventh Street 123 Ninth Street 20 Eleventh Street If I use the command "SELECT Address1 FROM USERS ORDER BY ORDER BY Address1 + 0", the result will be this (since MySQL would sort by the leading numbers): 19 Seventh Street 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street What I am instead trying to accomplish would be output that looks like this: 20 Eleventh Street 48 Fifth Avenue 100 Fifth Avenue 123 Ninth Street 19 Seventh Street Essentially, I need an ORDER BY clause that allows me to first sort by the street name and then will sort each individual house number per street name in increasing order. What can I do to accomplish this without creating a separate column for each street address and street name?
View Replies !
Sorting By Count(*)
how to sort records. I am trying to count the number of occurrences of an item in a column and then sort my recordset using that column. For some reason it works when I try it in phpMyAdmin but not when I send it through PHP. Any idea on what I might be doing wrong?Here is the example; SELECT COUNT(id) AS count, name, [other fields] FROM table GROUP BY name ORDER BY count DESC
View Replies !
Sorting Question
Is there a quick way to find an item's position in a set of fields using pure mysql instead of php array manipulation. For instance: I have a high score list composed of x number of entries. When a new score comes in I would like to find it's position within the list, and report back something like "you placed 4th out of 100,000 high scores" I can do this using PHP, but it seems like there could be a way to set up a schema to handle this more elegantly and place less strain on the server.
View Replies !
Repair By Sorting
I have an application which uses MySQL to manipulate large amounts of data for short periods of time. The data set contains 13 columns each of which is indexed and typically about 30 Million rows. I create a new table, load the data in to the table using load local infile from a CSV and then add indexes to each of the columns; the columns are a combination of integer, date and text. The text columns have a simple index and a full text index. I need to be able to sort on each column (in a grid - I use DevExpress) and search on the textual fields. I appreciate that getting the data into MySQL and creating the indexes is never going to be quick but I need to optimise this process as much as possible. Typically a data set would be loaded and viewed for a few hours and then a new dataset loaded and the process repeated. When the index process is underway I get the above state displayed by show processlist and wondered if this is a problem. The whole load/index process takes about an hour and I would like to improve on this if I can. I am also looking for suggestions as to how I may be able to best optimise this. I use a dedicated server and the MyISAM engine.
View Replies !
|
TRACKER
