MYSQL :: Is There A Way To Get A Negative Time Value To Display Rather Than Return NULL?

Time Value Calculation That Results In A Negative
here's my code:

SELECT *, SEC_TO_TIME(TIME_TO_SEC(SEC_TO_TIME(SUM(TIME_to_SEC(phour)))-SEC_TO_TIME(SUM(TIME_to_SEC(mhour))))) AS Balance from toil.User join toil.toil on user.userid = toil.userid where name = 'X'

FYI I'm working on a database to store details of Time Off In Leiu worked by staff (toil)

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 !

Display Results Even When NULL
Code:
SELECT
*
FROM
calendar_types,
calendar_allowances
WHERE
calendar_allowances.user = 'user'
AND
calendar_types.id = calendar_allow.type

View Replies !

IF NULL Return Two Rows Of The Value
Looking for a little help, using MySQL5.

I am doing a lookup from a keyword table to see top 10 most repeated keywords:

MySQL Code:
SELECT keyword, count(*)Â FROM table_name GROUP BY keyword ORDER BY count(*) desc LIMIT 10;

Which works just fine. However, sometimes there is a null in the keyword field, which I want to replace a default keyword value.

MySQL Code:
SELECT ifnull(keywords, 'defaultkeyword'), count(*)Â FROM table_name GROUP BY keyword ORDER BY count(*) desc LIMIT 10;

The problem is that in the table there is already 'defaultkeyword' so when the query is executed the returned results look something like

defaultkeyword | 1000
some keyword | 950
defaultkeyword | 800
....

since the null was replaced by 'defaultkeyword' how can I make MySQL aggregate the two into one result row so instead it returns

defaultkeyword | 1800
some keyword | 950

View Replies !

Echo/Return Value When Row Null And Otherwise Not In Array?
SELECT tag_id
FROM blog_tags
WHERE tag_name = 'exists1'
OR tag_name = '!exist'
OR tag_name = 'exists3'

Let's this returns 'exists1','exists3'. When I later try to match the arrays 'exists3' and on (from this array) will mismatch in a PHP loop.

rather have MySQL return...
'exists1','is_null','exists3'

View Replies !

Query To Display A Record By Recent Time
Many users uploaded their files to my mysql table through php script,
my table having the details of uploading time, file name, & uploader name.

i need to find the recent uploaded file for all uploaders.

i tried with this query,

select file_name,uploded_by,MAX(date_time) from upload group by uploded_by;

it is giving the recent time, but it is not giving the latest file, it is showing first uploaded file.

View Replies !

Inserting Time As Null
I've really really really confused and really need your help please!

I'm having trouble trying to insert data from a form into a mysql db, namely time. If a user only enters data for one day and leaves the other days of the week blank then it automatically defaults to 00:00:00.

How do I tell it to default the insert to nothing, so that when I fetch the data and display it in a php echo it will only show the days with info.

I hope I've explained that properly? Maybe I'm going about it the wrong way?

View Replies !

Negative Value Is Sql
i have a query like this

select (a+b+c)-(d+e) where some condition

this fetches one record and the values in the table are like this

a=0.000

b=0.720

c=0.000

d=0.620

e=0.100

above query gives me result as -0.000

although the answer shld be 0.000

View Replies !

Negative Numbers
What is the correct definition for a column to allow negative decimal numbers? I've been using float 5,2 but when the numbers come into the table they are shown as 0. If I look at the console, it does show the negative numbers being imported, but content shows no value.

View Replies !

Sum With Negative Numbers
I have a table with two integer fields (field1 and field2).

field1 = 2
field2 = 5

Then, i make the following SQL:

select field1 - field2 from test;

It returns a giant positive number instead of the -3 witch is the right answer.

Why is that ?

View Replies !

Age Returns Negative
Why is my select statement returns negative
SELECT table.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, FROM personnel INNER JOIN organization ON personnel.name = organization.name WHERE organization.type = 'public';

birth data is 1995-05-15

View Replies !

Equal To 0 If Negative
update players_parts set

part_durability = (part_durability-(part_original_durability*4/100)),
part_current_durability = (part_current_durability-(part_current_durability*4/100))

where player_part_id = '".$player_part_id."' and player_id='".$player_id."'

View Replies !

Negative Value In A Table
I am trying to enter a value in a field set to INT(11), but it will not allow negative numbers. What should this field be to allow such a value?

View Replies !

Timediff() And Negative Results
timediff('2006-03-08 22:38:42','2006-03-09 08:47:03') = -13:51:39

How can I make it not output negative numbers when the days increase?

View Replies !

Negative Number To Store
for some reason I can't get a negative float to store in my database. I have tried using almost every field type, quotes/non quotes, and done a ton of searching. I just can't figure it out.All I am trying to do is store longitudes and latitudes.

View Replies !

Sum Just Negative Resuls In One Query?
how can i sum two field and get the just negative results in one query?
for example
=============
a b
== ==== =====
sum( 50 - 70 ) = -20
sum( 20 - 10 ) = 10
sum( 80 - 150 ) = -70
general total = 80
negative total = -90 ***** how can i get these negative and positive
positive total = 10 ***** totals?

View Replies !

ORDER BY With Negative Results
i have some problem with ORDER BY with negative results

when i order by some fields that the fields which has negative results should be come first but it is not coming right

for example

results for field `general total`
a = 0
b = -10
c = 0
d = 20
e = 30

when i order the statement according to field ` general total`
it should come like

b
a
c
d
e

but it is coming

a
b
c
d
e

where i am doing wrong? what should i do?

View Replies !

Autoincrement And Negative Values
Hope someone can help me with a query. I am using mysql 3.23 and have a table with a negative value in the Autoincrement key field, i.e. user_id = '-3' when I look at the next autoincrement value it is set to 2,147,483,647 this is apparently the maximum value and is apparently because the negative value confuses the autoincrement.

Aside from the fact that I need to upgrade, does version 4 or 5 accept(or ignore) negative values ?

View Replies !

Multipling By Negative Numbers
how do i mulitiple by a negative numbers, if i have

Select This.That*-50 the it multiples it by 50 not minus 50.

The value in This.That will either be a 1 or a zero. So if its a zero multipling it by -50 will give me zero, multiplingit by one with giveme -50, which is what i want.

How do i solve this?

View Replies !

MySQL Ordering Negative Numbers.
i am using mysql to find out how out of date a time is. the sql fins out how lonf the entry date is over what it should be for its category. the sql needs to return a number format with 0 decimal places. it returns in the right order without the format() function but it seems to go all crazy when i use it to number_format it. is this a known but or am i doing something wrong? Code:

View Replies !

Is There An 'Is Negative' MySQL Query Function?
I have a query, as shown below. Its a grouped query and I have a column called quantity in a products table. If the value in this table drops below zero I want the query to take zero as the quantity. Is this possible?.....

View Replies !

Subtracting Two Columns Not Giving Negative Result
I now have a caching table which has

club_id | date | rank

so by left joining on the rank for last week to the rank for this week I can show

name of club | current rank | last weeks rank

now I want an extra column which is the move between this week and last week.

I am using this SQL:

SELECT *, (dt.lastweek - dt.thisweek) AS move FROM (...) AS dt

But it returns the following result: ...

View Replies !

Sort Not Handling Negative Numbers Correctly
In the table in my database I have a column of numbers ranging from -3.3 to 7.1. When I sort the column using (SELECT * FROM mytable ORDER BY mytable;) the column gets sorted as follows: -1.9, -3.3, 1.7, 2.2, 7.1 I streamlined the list as there are actually over 500 numbers between -3.3 and 7.1. The problem I am having is that when I SORT mysql is recognizing -1.9 as a smaller number than -3.3.

When I try to find the minimum number in the column using (SELECT MIN(mycolumn) FROM mytable;) mysql also displays -1.9 as the minimum number.

Is this a known problem with mysql or am I doing something wrong?

View Replies !

MySQL 4.0.17 Data Scrambles, Not-null Fields Become Null, Etc.
I'm having the most bizarre problem with PHP/MySQL that I've ever
faced and it's an urgent matter (of course) to try to fix it ASAP.

We have one record inserted into a table with 75 columns, whereby half
of the columns are not-null, but for some bizarre reason, the fields
are null. The rest of the columns that are null are completely
scrambled (e.g. "first_name" is in "address2", "fax" is in "url", "url"
is in "last_name", etc.)

This just recently occurred and seems to have no history otherwise. It
occurred sometime on Saturday when the record was inserted but logs
indicate no unusual activity at that time.

Has anyone seen this before and found a way to prevent it from
occurring again?

View Replies !

Incorrect Integer Value: 'NULL' For Column Even If NULL Is Allowed
mysql> desc my_table;
+------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| id | varchar(19) | NO | PRI | | |
| value1 | int(11) | YES | | NULL | |
| value2 | int(11) | YES | | NULL | |
+------------+---------------------+------+-----+---------+-------+

I want to import data from a file that looks like this:
9bcd6f7b47a30fb8_01;NULL;NULL

With following command:
mysql> load data infile 'filename' into table my_table fields terminated by ';';

And get following error:
ERROR 1366 (HY000): Incorrect integer value: 'NULL' for column value1'' at row 1

So the system denies me importing NULL values into a field where NULL is allowed.

View Replies !

MYSQL Removing Negative Signs For No Apparant Reason!
I've got a problem with an SQL query that is behaving badly. Its probably really obvious, but I can't see the problem!

I have a table with 3 fields:

money bigint(20)
income int(12)
userID mediumint(7)

I have a row that has a negative money and a value of 0 for money as well as an userID field as a primary key. Code:

View Replies !

Order By Both Positive And Negative Numbers, Format Issue
I have a column that contains numbers both positive and negative, e.i 5, 7, -2, 1, -8, 6.

On a webpage that sorts these columns using php, this commands are used to sort data, but I can't seem to get it to sort properly:

order by column desc';

result:

-10
-9
-47
-3
5.
30
2
1

order by column asc';

result:

1
2
25
3
8
-1
-2
-3
-55
-6

Desired Results

7
4
3
2
1
-4
-6
-9

and

-9
-3
-1
2
3
4
7

View Replies !

Buggy Select Involving Calculated Negative Values
SELECT ((avg_rating - 300) * total_ratings) as rating_score, sites.*
FROM sites
ORDER BY rating_score DESC

Note: avg_rating is on a scale from 100-500

Returns rows like:

rating_score: 18446744073709551520
avg_rating: 292
total_ratings: 12

By my calculations, (292 - 300) * 12 = -96

What's the deal here?

Here's the table creation SQL if needed:

CREATE TABLE `sites` (
`id` int(8) unsigned NOT NULL auto_increment,
`name` varchar(32) NOT NULL default '',
`url` varchar(32) NOT NULL default '',
`mature` char(2) NOT NULL default '',
`t_type` varchar(2) NOT NULL,
`t_preset` varchar(24) NOT NULL,
`description` text NOT NULL,
`guest_download` varchar(2) NOT NULL,
`vote_link` varchar(128) NOT NULL default '',
`date_format` varchar(32) NOT NULL default '',
`banner` tinytext NOT NULL,
`banner_type` char(2) NOT NULL default '',
`banner_width` varchar(4) NOT NULL default '',
`banner_height` varchar(4) NOT NULL default '',
`time_added` int(10) NOT NULL default Ɔ',
`allow_guests` char(2) NOT NULL default Ƈ',
`display_news_posts` char(3) NOT NULL default Ǝ',
`styles` varchar(12) NOT NULL,
`genres` varchar(12) NOT NULL,
`formats` varchar(12) NOT NULL,
`avg_rating` int(4) unsigned NOT NULL,
`total_ratings` int(7) unsigned NOT NULL,
`comic_count` int(4) unsigned NOT NULL,
`fav_count` int(6) unsigned NOT NULL,
`last_update` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `time_added` (`time_added`),
KEY `avg_rating` (`avg_rating`),
KEY `comic_count` (`comic_count`),
KEY `fav_count` (`fav_count`),
KEY `url_id___added_by_DreamHost` (`url`,`id`),
KEY `last_update` (`last_update`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

View Replies !

Getting Null Values From A TEXT NOT NULL Field
I have created a table with a field labeled Description which is a TEXT field which does not allow null values. It's defined with

Description TEXT NOT NULL,

However, when empty strings are stored in the Description field, I get NULLs back when querying the database. I'm writing an ASP application which connects to the MySQL database through ADODB. The MySQL version is 5.0.45. The engine is InnoDB.

View Replies !

CREATE TABLE :: Null And Not Null
I recently created a new table in a database and wanted some fields to accept nulls (NULL) and others to not accept nulls (NOT NULL). The table was built correctly, however, when I went to edit a row in the Query Window of MySQL Browser it allowed me to enter in the row when the NOT NULL fields had no values attached to them - this should fail and stop me from entering a row.

Here is the script for the table I created;

CREATE TABLE SLG (
SLG_ID int(11) NOT NULL auto_increment,
SLG_CODE CHAR(1) NOT NULL,
SLG_DESCRIPTION VARCHAR(255) NOT NULL,
SLG_DATE_CREATED DATETIME NOT NULL,
SLG_DATE_MODIFIED DATETIME NOT NULL,
PRIMARY KEY (SLG_ID)
);

When I apply the changes in this window it just enters NULLS into all the other fields. Is this a Query Browser issue. I tried doing the same with an INSERT statement and received the same result.

Also - related to creating a table - is it possible to create global Defaults on a database and then reference those in the DEFAULT statement of the CREATE TABLE command. MS SQL has a system function that can be executed after a table is created to bind a global default to a specific column.

View Replies !

Insert Null To Not Null Column
I have build a replication environment in my environment. the master is MySQL 5.0.24a-standard-log and the slave is 5.1.30-log. the configuration is not the same between them.

I have found a replicaiton error in the slave today says: .....

View Replies !

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 !

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 !

NULL And NOT NULL Settings
I have a MySQL table that stores name and age of a person. The name field needs to have a value; the age field is optional.I'm quite confused now with the NULL and NOT NULL settings... It seems that neither one does what I want (rejecting an INSERT query where the name field is empty)...So what exactly do I have to do?

View Replies !

VARCHAR Null/Not Null
I'm having a problem with a table that is already created - I want insert attempts to FAIL when the column for 'firstname' is empty (a VARCHAR field). Currently, the column is set to Null=YES and a Default of 'NULL'. This allows records to be inserted with no 'firstname' value (NULL appears in the column). If I change it in phpmyadmin 'structure' to Null=NO and Default of [empty field] then it allows records to be inserted with no 'firstname', and 'firstname' shows empty in the column

View Replies !

PK Field Not Null Is Being Null
I m newbie. I made a MySQL database table and in the primary key field I set the data type VAR CHAR.I also set it not null .BUT still I can insert blank values in this PRIMARY KEY field. If I just blank my primary key field by this query- Insert Tablename Values ('','','','','',''); (if my table has 6 fields).HOW IS IT POSSIBLE. How can PK field allow blank value when I set not null. HOW TO SOLVE IT.

2. I use a MySQL GUI Tools from http://dev.mysql.com/downloads/. In Table viewer of Query browser, each time I insert the first field, it sets the datatype to INTEGER,NOT NULL, AUTO INCREMENT. But I change it to VARCHAR as needed. Do I have to/should use Integer type in PK field and can Varchar be incremented.

3.Can I set user privilege to a particular row (on a particular PK); HOW?

4. Is it correct/incorrect that the name of the foreign key has to be the same as the name of the corresponding PK. I saw that in MySQL I can easily change the FK name to anything else and then just make a reference of FK to the PK.

5. How can I make a one-to-one relationship in MySQL;

View Replies !

Setting Null To Not Null How?
i have a mysql table.. (duh!)

a field in it is set to null and i want to unset it to make it not null.

how do i do that ? by default it takes null values (its weird.. default is null while null column is a no.. so it cant take null values!)

View Replies !

Different Timestamps For Time Of Creation And Time Of Update
What is the syntax to be used in MySQL when you want one timestamp to be set when the record is created, and the other only when it is updated?

I tried something like that but got a syntax error.

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 !

Time And The Change Of Time For The Summer
I have a xampp server with mysql and php. A few days ago the time changed to the "summer time", one hour plus but the curtime() of mysql did not change.

Is there a way to change also this curtime() automatically?

View Replies !

Display
SELECT t1.*
FROM tbl_data AS t1
INNER JOIN (
SELECT t2.lastname, t2.firstname, COUNT(*) AS cnt
FROM tbl_data AS t2
GROUP BY t2.lastname, t2.firstname
HAVING cnt > 1 ) AS t2 ON ( t2.lastname = t1.lastname AND t2.firstname = t1.firstname)

This is my recordset that displays all my database records. How can I change it so it only displays lastname that starts with the letter A. That is, I want only the rows that contain last names like Adams, Anderson, Appison, etc.

View Replies !

Display Particular ID First
I have a products table with a primary "id" field. One of my products id=27 needs to be displayed first then ORDER BY name DESC.

Is there a way to do this via the query - I know how to do it with PHP but would like to find a simple mysql alternative.

View Replies !

Display Column
i'm a beginer to PHP and MySql and I was woundering if I could display all of the different values in a table column without show dupelicates. One examle of what im try to achieve is

ID | ARTIST_NAME | ALBUM_NAME | TRACK_NAME

now lets assume that I have registered 10 albums into my database each with 10 tracks. If I try to display the different album names, I would get one album name followed by 9 dupes. Is there a way of just showing 1?
(note in the real database not all album have 10 tracks so a script that takes away 9 would not work!)

View Replies !