Datatype TIME - Select Statement Format
The table I have has two columns of ID and INTERVALS. The INTERVALS is datatype TIME. Whenever I do a select I always get the table back showing the time in the format HH:MM:SS like for 11am I get back 11:00:00. I only want HH:MM like 11:00. How do I achieve this? I have tried to limit to 2 decimal places but that has not worked so far. ...
View Complete Forum Thread with Replies
Related Forum Messages:
Timestamp :: No Datetime Format And Datatype
Since upgrading our server and now having mysql 4.1.14, we have a problem with our timestamp column. We need it to be in 14 digit mysql format, not datetime format. Our affiliate software will not run this way for certain functions. Is there a workaround for this, or do we need to go back to our previous version?
View Replies !
Select Statement Where Time <= 5min Ago
i tried many different MySQL fucntions and looked through the MySQL manual, and it didnt help me any. What i want to do is select all rows from a table where my time column(DATETIME) is less then or equal to 5 minutes ago from now().
View Replies !
Time Datatype And Error 1064
Time and date are still giving me grief as I try to insert some data into a table. The query I'm running is: INSERT INTO security VALUES (24623,2003-07-04,18:23,10:03); The datatypes are char, date, time, time #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':23, 10:03)' at line 1 I've been into the manual 10.3.2 TIme -
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 !
Time Format
As a string in 'D HH:MM:SS.fraction' format. You can also use one of the following ``relaxed'' syntaxes: 'HH:MM:SS.fraction', 'HH:MM:SS', 'HH:MM', 'D HH:MM:SS', 'D HH:MM', 'D HH', or 'SS'. Here D represents days and can have a value from 0 to 34. Note that MySQL doesn't yet store the fraction part. I want to format the time to HH:MM, but I tried a variety of queries like: create table test(now time default '00:00'); create table test(now time default 'HH:MM'); create table test(now time default HH:MM); create table test(now time default HH:MM.fraction); create table test(now time('00:00')); create table test(now time('HH:MM')); With and without quotes, with and without parentheses - they don't work. Either it gives an error or it doesn't but still adds the trailing ':00' for seconds. How is this supposed to work?
View Replies !
Change Time Format
i need to take a "time" field entry, ie. 09:00:00, and have it display as 9:00am - or 22:00:00 as 10:00pm. the only answers i've been able to find use "date" functions - and the times i'm using aren't tied to dates.
View Replies !
DATE Format :: How To Put Time?
I am using PHP myadmin to setup my mySQL database. The format I choose to store my date text field was DATETIME. How can I choose a different format for the DATE? All I want is a: 12-7-2007 format and don't want to put in a time but it's expecting the time as well.
View Replies !
Date/Time Format From CSV
I have a rather large csv file that includes many dates that I would like to import into MySQL. The problem that I am having is that the dates are DD/MM/YYYY HH:MM:SS, and MySQL does not accept them in its YYYY-MM-DD HH:MM:SS format. Any ideas how to convert this properly?
View Replies !
Access->MySql Time Format
I am in the middle of transitioning some databases from Access to MYSQL. Until our apps can be rewritten I have created an app that moves data from the current Access db to MYSQL on a daily basis. Problem: All fields come across ok except the time fields in Access. Times in Access such as 12:37:01 AM are appearing in MYSQL as 37:01:00. The field in MYSQL is Time with default 00:00:00. Has anybody encountered this or have a solution? I use ADO and VB as the application mechanisms.
View Replies !
Storing Time AM / PM / 24 Hour Format
I need to store time in several tables and im trying to find out the best way to do it. I need to store the time in this format HH:MM (or HH:MM:SS). I'm thinking of using the TIME field in MySQL 4.1. I need to show the time with AM/PM, so i guess i should store it in a 24hr format and use DATE_FORMAT to show it with AM/PM...right? And i will need to show them chronologically ... how would i do that?
View Replies !
Datetime :: 12 And 24 Hours Time Format
I am using webservice to download data from mySQL to SQL Server. I noticed in sql server datetime format makes the hour 12 to 00. for example 2008-06-12 00:40:47.000 This is really 2008-06-12 12:40:47.000 My question is how does mySQL treats 24 hours format. with 12 or 0 for the hour "12".
View Replies !
Change The Default Date Time Format
I am using mysql 5.1 . In that one default date time format is yyyy-mm-dd. But I want to use it as dd-mm-yyyy. Can you anybody explain me how to change the default date time format. not only in that format in any format.
View Replies !
Sort By Time In Text String Field (hh:mm:am/pm Format)
I have a group of records that I need to sort by time field. The time field is a text string (should've been a date/time - I know -doh!) I was wondering if anyone knew how to write a query to sort in Ascending order with the earliest appearing first. The times are stored as follows: 10:30pm 11:30pm etc.
View Replies !
Format Date In A Select Query
I'm storing dates in a db using the DATE-datatype in the YYYY-MM-DD format, now i want to show the dates on my webpage in the european DD-MM-YYYY format. I have written the following SELECT-query but it doesn't seem to work. The date is not shown when i show the results of the query. The column where the dates are stored is called "startdate". MySQL $query = "SELECT date_format(startdate, '%d %c, %Y'),cat,locatienaam,adres,postcode,stad,telefoon,contactpersoon FROM datasheet "; this is what I do with the result of the query MySQL PHP $result = mysql_query($query); while($row = mysql_fetch_array($result, MYSQL_ASSOC)){ echo "<h2>{$row['locatienaam']} </h2>" . "<h2>{$row['startdate']}</h2>" . "<p>{$row['omschrijving']}</p> <br><br>";} And it doesn't show the date when I use the SELECT query shown in the first post of this thread.
View Replies !
Would 'binary' Modifier In A Create Table Statement Lead To Longer Exection Time
a table tb_A is created as Code: create table tb_A (Word varchar(20) binary NOT NULL);or created as Code: create table tb_A (Word varchar(20) NOT NULL); There is another table tb_B. It is created as Code: create table tb_B (Word varchar(20) binary NOT NULL);or as Code: create table tb_B (Word varchar(20) NOT NULL); do the following query over tb_A and tb_B Code: SELECT Word FROM tb_A, tb_B WHERE tb_A.Word = tb_B.Word; I wonder if the binary modifier would cause longer execution time?
View Replies !
Select Statement Question (nested Select?)
I have a DB containing 3 fields fullname, inext, and outext. I need to see all the records that have a duplicate entry in inext. I know I can do a distinct query on the inext column but that only gives me the unique ones I need all the different duplicate records.
View Replies !
Combining SELECT Statements Into One SELECT Statement.
I want to take the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN authuser ON name.empnumber = authuser.uname AND authuser.team = 'PHQ' ORDER BY name.lastname, name.firstname; and the results from: SELECT name.empnumber, name.firstname, name.lastname FROM name INNER JOIN crew_attendance_6QJ ON name.empnumber = crew_attendance_6QJ.empno ORDER BY name.lastname, name.firstname; And combine them into one query that outputs all of the results both queries would output. Then order those results. So far I have come up with:
View Replies !
Select Statement!
I have a simple query that is similar to this one: SELECT distinct b.username from permissions a, users b, links c where a.user_id = b.id and c.information = 14; In this case, it selects the all users details from table 'user' that are permissioned to access a certain information with id = 14. The problem is that I need to select the users that are not permissioned but I have sql v3.23 and I canot use union. In other words, I need to do a select b.username from users b where b.username not in "the result" of the last select.
View Replies !
One Select Statement
I have a table with the fields id | titleid | catid Each title can have more than 1 catid so it's possible for the fields to look like this: id | titleid | catid 1 1 1 2 1 2 3 2 1 Now I want to select any rows where titleid matched more than one catid. In this example I would like to select titleid 1 because it is in catid 1 and catid 2 but I can't seem to figure out a statement that properly says "give me any titleid that has a catid of 1 AND a catid of 2"
View Replies !
SELECT Statement With OR
I need some help with this select statement: SELECT * FROM gallery WHERE type = 'skate' AND park = 'Both' OR park = 'Park 1' I just need it to look at one table and grab all the rows that have a type of 'skate' and have a park value of either 'both' or 'milwaukee'. I think the OR is throwing it off.
View Replies !
Regarding Select Statement
I am retrieving data from two tables using the UNION statement and i am thinking of having a field in my resultset(this field is not found in the database) to distinguish the data from two tables.Is it possible to set a fixed value for a particular field using the select statement?For example, a field value of 'buy' and 'sell' values are used to distinguish between the two tables.
View Replies !
Select And A Max In The One Statement
Can you combine a select and a max in the one select statement? Eg,. I want to select like select ids from table_name where ids > $idsvariable... order by ids.... but I also want to grab the highest ids number so I can put $idsvariable (max) into a table so next time it selects only the ids higher than the last time the select was run! So if ids were: 2 5 6 9 12 14 15 then I'd like to get 15 as the max as well... any examples.
View Replies !
Select Nav Statement
I have a nav table in my database that grabs information to put into the navi menu, this is dependant on user access etc... i have an item in my table that is in zone "members" and it is marked for permission Z. Although when i run the query it dont pick it up.... these are the queries that ive tried... SELECT * FROM acwe_nav WHERE privs LIKE '%A%B%C%D%E%F%X%Y%Z%' AND zone='members' ORDER BY weight; SELECT * FROM acwe_nav WHERE privs LIKE '%ABCDEFXYZ%' AND zone='members' ORDER BY weight;
View Replies !
Else If In Select Statement
i have a bunch of records that i want to pull on the basis of eg SELECT lat, lng, html, label, icontype FROM ade WHERE icontype='" . $_POST['name'] . "'"; but i'd like to make the first query and also another on an "if else" basis to cover various bases if i post a query where the 'name' isnt an icontype, i want it to look in the "section" field and pull those records matching a certain value ie all records that have a value of "5" is it possible to do something like this SELECT lat, lng, html, label, icontype FROM ade where icontype='" . $_POST['name'] . "' ELSE IF section='" . $_POST['name'] . "' ELSE IF catid='" . $_POST['name'] . "'";
View Replies !
Is There Something Like A SELECT ALL BUT Statement?
Usually the tables I have to handel have a high number of columns. Additionally, columns are deleted and added dynamically. Most calls made on these tables are select statements: Consider two tables t1 and t2. A natural join results in the columns a,b,c,d,e,f,g,h,z. A usual select statement is the following: SELECT a,b,c,d,e,f,g,SUM(z) FROM t1 NATURAL JOIN t2 GROUP BY a,b,c,d,e,f,g This way the column h could be "eliminated". As you can see the select and the group by clause can become fairly long. What I am looking for is a clever way to say "Group by everything but h and z". Is that possible?
View Replies !
Select Statement MIN()
SELECT * FROM Distance, LorryDetails WHERE cavan = (Select min (cavan) From Distance where cavan>0); here is the results i am getting source ,Cavan, Athlone, Cork, Galway ,LorryID, LorryReg, Position Athlone 90 0 180 80 3 02-CN-8001 Cork Athlone 90 0 180 80 7 00-CN-1000 Cavan Athlone 90 0 180 80 10 02-G-8001 Galway What i want to try and do is compare the Values with the Column names and get the shortest path. for example when you compare the position cort with the column cork it is 180, cavan is 90, and galway is 80. Galway is the nearest to athlone so i want to narrow down the results to just give me back the row where the position is equal to Galway. Can anyone help me with this.
View Replies !
Select Statement Within Another?
Tag_id::Img_id::Tag::counter 001::1::Tree::70 002::2::Tree::50 003::3::Pond::30 004::4::Flower::20 005::5::pink::60 lets say the above is my table. this is my current query: SELECT tag, max(counter) FROM tags GROUP BY tag DESC limit 3 this is the result I get: tree 70 pond 30 pink 60 here is can someone pls tell me a way to get the total of the counter of all the occurrences of a particular tag(than the max(counter) ). so that it'l be tree 120 pond 30 pink 60
View Replies !
SELECT With An AS Statement
I'm using a LEFT JOIN to mash two tables together. It works fine. I'm feeding this result into PHP's mysql_fetch_assoc. mysql_fetch_assoc doesn't handle columns with duplicate names very well, and these two tables do have duplicate names. So, I am using AS to assign an alias to these duplicate column names. But, these tables have about 20 fields each, and I can't figure out how to use my SELECT statement so that it selects all of the fields from tables (SELECT * FROM . . . ) but also assigns an alias to a specific field.
View Replies !
SELECT MIN, MAX And IF Statement
I have a few entries with a start timestamp, and an end timestamp. What I'd like to know is the earliest start date, and the latest end date, to define the complete time span of the selected entries. Example: id, date_start, date_end: 1, 0000-00-00 00:00:00, 2009-02-07 12:00:00 2, 2009-01-01 12:00:00, 2009-01-05 12:00:00 3, 0000-00-00 00:00:00, 0000-00-00 00:00:00 As you can see, it's possible that a start_date and/or end_date aren't inserted. If I now select the MIN(start_date) and the MAX(end_date) from the above entries: SELECT MIN(start_date) AS start, MAX(end_date) AS end FROM entries WHERE ... The result is: start = 0000-00-00 00:00:00 end = 2009-02-07 12:00:00 What I'd like to return, is the lowest possible date_start, in case a date_start has been entered. In the example above, this would be 2009-01-01 12:00:00, because that's the only date_start that isn't NULL. I tried this: SELECT IF(start_date>0, MIN(start_date), NULL), etc...
View Replies !
SELECT With IF Statement(?)
# Table structure for table `db_emails` # CREATE TABLE `db_emails` ( `id` int(10) NOT NULL auto_increment, `user_id` int(10) NOT NULL default '0', `email` varchar(255) NOT NULL default '', `main` int(1) NOT NULL default '0', `list` int(2) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; # # Dumping data for table `db_emails` # INSERT INTO `db_emails` VALUES (1, 1, 'a.a@a.se', 1, 1); INSERT INTO `db_emails` VALUES (2, 1, 'aa.aa@a.se', 0, 2); INSERT INTO `db_emails` VALUES (3, 2, 'b.b@b.se', 0, 1); INSERT INTO `db_emails` VALUES (4, 2, 'bb.bb@b.se', 0, 2) So, now to my question. How can i do a SELECT where i want all email adresses BUT only the main if main=1 and all if main=0. The result for this dataset should be: a.a@a.se b.b@b.se bb.bb@b.se
View Replies !
Select Statement
How can I do a select statement for this problem: If a in ('1', '6', '11', '16', '21', '26', '31', '36', '41', '46', '51', '56') then '1' as ‘stemp’ If a in ('2', '7', '12', '17', '22', '27', '32', '37', '42', '47', '52', '57') then '3' as ‘stemp’ If a in ('3', '8', '13', '18', '23', '28', '33', '38', '43', '48', '53', '58') then '5' as ‘stemp’ If a in ('4', '9', '14', '19', '14', '29', '34', '39', '44', '49', '54', '59') then '7' as ‘stemp’ If a in ('5', '10', '15', '20', '25', '30', '35', '40', '45', '50', '55', '60') then '9' as ‘stemp’ I have this possibilities as a result.
View Replies !
Select Statement
I have a table containing names of people in the neighborhood. The table only contains first name and family names. How to get lists of first names grouped by the same family name ? This is similar to getting "sum" of salary grouped by a certain condition. Unfortunately, "sum" does not work for strings.
View Replies !
Need Help With SELECT Statement
I have 2 simple tables: emps and rprts. Table emps has: "ID", "name" (simple string), and some other irrelevant fields. Table rprts has: "ID", "emp_id" (indication of "ID" in emps table), "rprt" (10-digits time), and "type" (boolean). I want to select the fields "ID" and "name" from emps, and in addition - for every selected row I want to select rprts's "type", where "rprt" (or "id", doesn't matter) value in the highest. For example: emps: id name ------------------ 1 jeff 2 wanda 3 brad rprts: id emp_id rprt type ------------------------------------------- 1 3 df 1 2 1 ie 1 3 2 ei 1 4 3 fd 0 5 2 tg 0 6 3 gf 1 For this data, the following should be selected: id name type ---------------------------- 1 jeff 1 2 wanda 0 3 brad 1 I tried several things, but I want to keep it simple and readable, so I'll understand if I'll read it sometime in the future.
View Replies !
Help Me With This Select Statement :)
I have two tables 1- articles table which contains the articleid and article_description 2- articles_translation table which contains translation_id, article_id(foreign key from the articles table) and translation_description I would like to select all the articles which aren't translated yet... How can i do this in MySQL?
View Replies !
Is Null Clause Takes A Lot Of Time But Is Not Null Statement Not
i have a query which takes 1 and half minute to fully execute. This query is following which return 2 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is null but if i remove the 'not' from where clause then it takes a fraction of seconds. query is following which takes fraction of second and it returns 3920 records select o.id, o.number, o.timest, o.receiptno, o.canedit, o.sessionid, o.voidorder, o.cashchange,p.amount from orders o left join payments p on (o.id=p.oid) where p.amount is not null
View Replies !
Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code: select date, `time` as timetime , time_format(`time','%l:%i %p') as Printtime, client, address from..........
View Replies !
Select Between Date And Time
I have field date and time respectively. I want to select date between (date1 and time1 > 17) and (date2 < 17) I tried two ways but doesn't work, please see code below. SELECT * from purchases_suppliers WHERE (date_invoice >= '2009/02/25' and time_invoice > 17) AND (date_invoice <= '2009/02/27' AND time_invoice < 17) SELECT * from purchases_suppliers WHERE date_invoice between ('2009/02/25' ANd time_invoice > 17) AND ('2009/02/27' AND time_invoice < 17 )
View Replies !
Update And Select Same Time
Code: UPDATE art SET art.art_writer = (SELECT writer.writer_name, art.art_author FROM writer, author WHERE art.art_author LIKE 'writer.writer_name') Is this query syantex is ok ? Where, art_writer is empty fild where I want to put correct value which is in writer table and it should similar to value in art_author filed.
View Replies !
|
TRACKER
