Generating DAILY Min&Max Over A Period Of Time, Based On A "DateTime" Field
how to print the minimum and maximum of a specific field, between two datetimes (both having the time 02:00:00) - so I did this:
WHERE ((tblvalues.dtLPDateTime)>="2006-01-01 02:00:00" And (tblvalues.dtLPDateTime)<"2006-01-03 02:00:00"))
Now I want to print those min and max values but for each day in this cycle, i mean, for this example, there would be two records like:
Day Min Max
2006-01-01 02:00:00 -> 2006-01-02 02:00:00 | 40 | 59
2006-01-02 02:00:00 -> 2006-01-03 02:00:00 | 49 | 68
where at this point i`m getting min=40 and max=68
View Complete Forum Thread with Replies
Related Forum Messages:
Datetime Field Updateing Time Only
How would I update the time only on a datetime field? The filed is: "2008-09-14 12:06:00" and I want to change it to "2008-09-14 12:16:00". If I use  update field_name set field_name = TIMEDIFF('2008-09-14 12:06:00', '2008-09-14 12:16:00');Â
View Replies !
Update Time Ony In Datetime Field
How do I update the time portion in a datetime type field? There are several rows containing various times and I want to make them all the same: 11:30:00. I have tried doing the following w/o success: update field set field = "date(field) 02:02:00"
View Replies !
Work Out Daily Interest Rate Based On 2 Tables
Hi I have the following DB structure charges cID [bigint] cpID [bigint] amount [decimal 3,2] date_charge [datetime] petitions pID[bigint] interestrate [decimal 3,2] date_claim [datetime] And need to do the following calculation: ($amount * ($date_claim - $date_charge) * ($interestrate / 36500)) The ($date_claim - $date_charge) section needs to work out the difference in days Is something like that possible?
View Replies !
Time Period Results
Can anyome tell me the php script to return data from a table which is less than 24 hours old. I have a timestamp on my table when data is added.
View Replies !
Dumping Data From A Certain Time Period
Every time I make changes to my MySQL database on my local PC, I have to dump the whole table on the server. This can get a bit annoying when the table contains thousands of records.Is there a way for MySQL to log all the updates you've made since a certain time and then use this file to dump it on the server?
View Replies !
Removing Data After A Certain Period Of Time
What I want to do is run a query that inserts data into my database, but removes itself on a set date. Is there any way to do this? Basically, I have something like this: INSERT INTO table (blah, blah2) VALUES (blah blah, 1) I would like to do something like: INSERT INTO table (blah, blah2) VALUES (blah blah, 1) {{{ INSERT CODE TO MAKE VALUES GO AWAY WHEN CURRENT TIME = SET TIME TO GO AWAY }}} There is an alternative to this if I have to. This is all for a PHP script, and instead of having MySQL automatically remove the data, I could add a time to remove to the table, insert the value for it in the INSERT query, and run a query on the index page consisting of something like <?php $timeToRemove = SELECT_TIME_TO_REMOVE_FROM_DB; if($currentTime => $timeToRemove) { REMOVE; } ?> so that every time someone hit the index page, it checks if it is time to remove the values from the DB and if it is, it does so. But of course, that adds an extra query/queries, and I'd like to avoid that. Edit: It may be useful to note that this is for a message board system, and I am dealing with making usergroup changes temporary. So instead of removing the values, perhaps it would be more useful if I could simply have MySQL reverse itself after a certain time has passed. But that seems unrealistic for some reason now that I think about it, so the alternative may be my best choice. Any suggestions from you all would be nice as well, if you've any ideas.
View Replies !
Mysql Select Based On Datetime
I am having an issue returning a known record based on a date field that is of datetime format from mysql. the data looks like this in mysql: 2006-01-26 20:01:45 here is the eror I receive: SQL error=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 ཐ:01:45' at line 1 this is the select statement I am using ("SELECT selections from your_show WHERE userid = $userid and date = ".rawurldecode($date)." "); $date is passed as 2006-01-26%2020:01:45
View Replies !
Compare Datetime Field With Todays Datetime
How do I compare a datetime field in the database with todays datetime... Is it: if rs("fieldname")<>getdate() then .... else (gotto set a cookie here) end if Somebody suggested I use cdate...what is tht for..is it to make sure they are both in the same format or something.. if so how do I use it?
View Replies !
Room Reservation: See If A Time Period Is Open For A Particular Room...
this is stumping me... i need to see if a time period is available for a something (in this case a room reservation). it's not as simple as starttime < end and endtime > start... for example a room is scheduled from 12:00 to 1:00pm and from 2:00 to 3:00pm. how can you check if a user entered: from 1:00pm to 2:30pm? i'm trying to UNION but that doesnt seem to work. subselect?
View Replies !
DateTime Without Time
I have a difficult case and need your advice: For managing events in my online application I need to be able to input the start and end. They can be inserted either as a date only or as a date and time both. And I save this data into the DATETIME format. The problem is, if only a date is inserted, MYSQL will put the missing time information automatically as '00:00:00' but I don't want this. Because it will display the time as '00:00:00' which is wrong. Is there no way to save into DATETIME format without the time?
View Replies !
Comparing Datetime And Time
I need to compare two values : the first one is in DATETIME format and the second one in TIME format. If I compare the two values with an usual operator, will MySQL extract the "time" part of the DATETIME and compare it to the TIME value? If not, what function can I use to extract the "time" value? I have noticed the existence of TIME(expr) function, that extracts the TIME value from 'expr'... but it works only if 'expr' is a string, not a DATETIME.
View Replies !
Selecting Based On Time Range
I'm writing a series of queries on a transactions table. I'd like to find all transactions from: Today Yesterday Last Week Last Month This Year The table contains a datetime field called txn_timestamp. What is the appropriate code to filter the table for each time window? SELECT transaction_id FROM transactions WHERE <?>
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 !
Update Just Time In Datetime Column
I have a datetime column called foo, it currently has dates in it with all times set to "00:00:00". I would like sql to update all the rows and set the time to "15:15:00" but leave the dates as they are today.
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 !
Function To Convert From DateTime To Relative Time
I have a DATETIME column and I looking to write a function which does the following: Takes in the DATETIME and compares it against the current time, and returns a string in any of the forms below, depending on the value of the DATETIME: 'You updated your settings 4 seconds ago.' 'You updated your settings 2 minutes ago.' 'You updated your settings 6 hours ago.' 'You updated your settings 12 days ago.' But its smart and automatically scales from seconds, to minutes, to hours, to days, rounding either up or down.
View Replies !
How To Save Accounts Based On Variable Amounts Of Time
I'm currently building a website where users can signup for various accounts. An account may last 30 days, 12 months or 24 months. If a user's account is about to expire, an email notification is send, via a CRON Job. I want to save the different account types in the database, but I'm not sure how to save the duration of an account. If it was all months, I could simply save ཈' or པ' as an INT, but it also has an account for 30 days, which is more flexible. Edit: It would also be nice if I could ORDER BY duration..
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Creating Datetime Data Object From Date And Time Objects
I have date and time objects that correspond to eachother. I want to add a certain amount of minutes and hours to them and get the resulting date and time. I was planning on creating a datetime datatype from the two and then using the date_add() function and then convert back; however, I do not know how to create a datetime object from two seperate date and time types and then back to seperate types, or whether it is even possible.
View Replies !
Change Value In Field Based On Order
I have a field that is an INT and can contain a value of 0 or greater. When the value is 0 it creates a unique situation and users of the site will sometimes change this to 0 on purpose because this value can have your item show up in the search results on the first page. Why, if a person orders the information ASC, it will show up first. I have to allow this field to be 0, long story, but I want to "punish" them if this is so. Based on the type of search (ASC or DESC) as the user can pick. I want those items to always show up last. Currently I am passing a variable that gets set via a form w/ that information, but I don't know how to check in the query how the variable is set. I was thinking CASE but I thought that only looks at a field in the db, not some variable that is being passed. Either way, I hope this is making sense. Here is the query (pretty simple). Code: ......
View Replies !
Adding A Key Field Based On Other Columns
I have a MYSQL db based on an excel sheet There's a membership number field that's mostly empty, there's five different levels of membership from full to part time and so on, each represented by a 'Y' in the relevant field, also a date field with the date that they first joined What I want to do is allocate a unique membership number for each member, a six figure number starting at 100001 upwards for the full members starting with the oldest memberships, 200001 upwards for the part time members, and so on.
View Replies !
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Replies !
Adding Up DATETIME Field
i'm struggling with updating session expire times in my login table. Can you tell me, how to correctly add an amount of seconds (after that the session expires) to a datetime field? when i do the insert for a new login, or an update, the expire field will alway end up containing zeros Code:
View Replies !
Datetime Field Math
I need the most optimal way to determine if the value of a datetime field is between the current time and 24 hour before current time. I have tried the subtime function in several ways, and can't seem to get it to work.
View Replies !
Default Value For DATETIME Field
I am trying to install a WordPress blog and the install script is failing as several of the tables contain a declaration like: Code: my_field datetime NOT NULL default '0000-00-00 00:00:00' I am getting the message "Invalid default value for 'my_field'". Is there is a setting I can change to allow for this declaration, bad as it may be?
View Replies !
Datetime Field Problem.
I need to have a datetime field that auto-updates itself in the format yyyy-mm-dd hh:mm:ss. Because of the formatting required I cannot use the timestamp field type. If I create a datetime field with the default value of now(). All I get is 0000-00-00 00:00:00. Is there a way to accomplish this WITHOUT passing the update through code. Meaning have the database update the record automatically and not have to do this through an update statement?
View Replies !
Comparing DateTime Field To Now()
I am trying to have a select query which compares a Datetime field to the current date/time, Now(). Basically I want something to this effect: SELECT Name FROM Lists WHERE expiration > 'Now()' Expiration is a datetime field. The problem is It returns every name on the list except for ones with a value of 0000-00-00 00:00:00 whether or not the date has passed already. I have tried many other things including but not limited to SELECT Name FROM Lists WHERE datediff(expiration,'Now()') > 0 SELECT Name FROM Lists WHERE timediff(expiration,'Now()') > 0 SELECT Name FROM Lists WHERE datediff('expiration','Now()') > '0' and many other variations on those. The above 3 just returned a null recordset. How can I make this work?
View Replies !
Upgraded To 4.0.22 Now Datetime Field Is Different
We will be replacing an older server which is running mysql 3.23 and figured we would upgrade mysql apache and php. Most everything seems ok except for our helpdesk software (written in house) in php. It calculates the difference between two datetime columns so we can see the time span of when a ticket gets updated. With the new version it fails and shows me an error like this: date1 has to be >= date2 in calcDateDiff(1099810800, 1099810995). Date2 is the date when the ticket was submitted, and date1 is when the ticket was last updated. With the newer mysql it seems to add a hidden value when the ticket is first created which causes the error. Depending on the function I use to calculate the difference it will return a number like -23:21:35 which is the approx. time since midnight and it was about 11:23 PM when I filled out a test ticket. After I update the ticket it adds the datetime stamp and everything computes just fine. I am not sure if this a mysql quirk or a php quirk since both were updated to newer versions. Could someone explain what might be the difference between the datetime fields in the 2 versions and how I might fix the problem if possible?
View Replies !
Searching DateTime Field
I'm having a bit of trouble getting this to work. What i have is a DateTime field in a table and need to pull the records for only a particular day (based on a variable passed through). I looked on the MySQL website and found the date and time functions but it states the following, DAYOFMONTH(date) Returns the day of the month for date, in the range 1 to 31: mysql> SELECT DAYOFMONTH('1998-02-03'); -> 3 Now how would i translate that into a workable PHP query? I tried the following with no luck, $Query = mysql_query("SELECT *,date_format(date, '%M, %d, %Y ( %r )')as date FROM $Table_diary WHERE DAYOFMONTH('date,$Day') && MONTH('date,$month')") or die(mysql_error());
View Replies !
Combining Multiple Records Into One Based On A Key Field
I'm really scratching my head over this one. I'm working with CSV data exported from Excel, which explains why it's a mess to begin with. Within a table (or via any other means someone might be able to recommend) I need to combine multiple records which share two like fields. (If that's not clear, the real-world explanation is below.....
View Replies !
Selecting All Records Without Dups Based On One Field?
I have a table with 5 fields, of which several sometimes have duplicates. example; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 4 | 7 | 3 | 9 | 122.165.177.211 | | 5 | 11 | 3 | 9 | 177.15.99.111 | | 6 | 24 | 3 | 17 | 122.165.177.211 | As you can see the ip address may obviously have dupe entries (as do other fields but I don't care if they are dupes) and I want to query the table and get all the fields but only one where there is dupe ip_address 's. So that I would get these results; | id | item | day | hour | ip_address | | 1 | 3 | 2 | 11 | 204.156.33.78 | | 2 | 7 | 2 | 15 | 122.165.177.211 | | 3 | 1 | 3 | 1 | 205.52.79.122 | | 5 | 11 | 3 | 9 | 177.15.99.111 |
View Replies !
Detecting Duplicates Based On One Field In A Table?
I can't seem to find an answer anywhere for this as most people want to delete duplicate records. I have a table, lots of fields, one of which is "title". I want mysql to return all the rows where "title" is the same. For instance if "title" was "Lord of the Rings" on id = 1 and id = 2, then the query would return both of the records, but ignore the others. It doesn't matter what the other field values are. I have found some SELECT COUNT( * ) AS num_entries, title FROM title_table WHERE title >1 GROUP BY title But this doesn't seem to work, although looks like it's on the right sort of lines? I'm not a Mysql expert, so my experience is mostly limited to the usual select, insert, update and delete queries!
View Replies !
Datetime Field Default Now() Fails
I have a mySQL database table with a column field datatype of datetime that I set up in the original create table statement like this: create table nnet_usermetadata ( .... nnet_record_entered datetime default 'now()' ) However, upon execution, each time a record is entered into nnet_usermetadata the results are: 00-00-0000 00:00:00 Is there a way I can always ensure that the current date and time are always entered into the datetime field?
View Replies !
Datetime Field Changed After ALTER
To archive when a row is added, I have a column called date_added which uses the data type DATETIME. I recently added a new column to my table "ALTER TABLE mytable ADD COLUMN mycolumn INT", and my stored dates have all been changed. In hindsight, was this to be expected? I am using PHP to both push data into MySQL and pull data from MySQL. To prevent such future blunders, is it good practice to store dates as CHAR data type, and actively store the date using PHP?
View Replies !
Datetime Field Format Add Slashes
I have a datetime field that currently appears like this: mm/dd/yyyy hh:mm:ss I need to convert the dates in this field to MySQL format: yyyy/mm/dd hh:mm:ss It's a matter of being able to move the 'yyyy' portion to the beginning of the data field (of course adding and removing a slash
View Replies !
DATETIME Field Conversion In Mysql
I know that there is a function in MYSQL to define a 'datetime' type column but when I am migrating a foxpro database to MYSQL type and when foxpro has the datetime column in the format: "08/12/1999 09:39:00", how would I convert this to mysql format while I am trying to load the data in the database(using LOAD command). I have used str_to_date functions in the past to convert the date formats between the two databases but how do we go about the datetime format.
View Replies !
Select Julian Day From Datetime Field
I am trying to build a SELECT query that allows the user to pull a specific day from a table of data. Currently, I have a year, day, and time field, so the user can SELECT * from TABLE where year = 2006 AND day = 245. I'm dropping these three fields in exchange for one datetime field (with the format yyyy-mm-dd hh:mm). Now when I select day(datetime) = 245 no records are found because I suspect that the days are being counted as 1 - 28, 30, and 31. Is there a simple way around this like a julian day function that is included in the SELECT statement?
View Replies !
Comparing DATETIME Field With Date...
I'd like to make query which compares two dates - one from database, ang second in php (date("Y-m-d H:i:s")). I'd like to make query which gives me back those rows where the date from db is more than three days older than current date.
View Replies !
Field Type :: Time Field With Decimal
I want to add times into a field in the form mm:ss.d such as 03:33.5 but when I use the time type it removes the decimal but I really need it to keep it in. At the moment I am using a char(10) type and converting it in my scripts. Is there a way I can add it in using the correct type? I couldn't see anything in the documentation on the time type except that you could enter it in the form I want but it will convert it.
View Replies !
Date Field, Time Field, Sort By Date And Time
I have a MySQL date field (e.g 13/12/2006) and a MySQL time field (e.g.13:00) in the same row. I'd like to join these two fields to make a date/time field (e.g. 13:00 13/12/2006) on the fly and to be able to sort the query results on the resulting field.
View Replies !
Updating A Database Field Based On A String Or Filename
I am using MySQL to store images that I serve up with Coppermine photo gallery. Each image is shown in low resolution format. I also store medium and high resolution versions of these files for download. There is a caption field where I put these links using BBcode. It looks something like this: Code:
View Replies !
Trigger That Fills In A Missing Foreign Key Based On Another Field
To provide an easy insert methods for our client, I'm trying to write a trigger that fills in a missing foreign key based on another field that is inserted. Basically, 'dr_market_id' is inserted and I'm trying to find 'market_id' by selecting the appropriate row in the markets table. area_codes_markets.market_id is foreign key to markets.id delimiter | CREATE TRIGGER area_codes_markets_ins BEFORE INSERT ON area_codes_markets FOR EACH ROW BEGIN DECLARE tmp_market_id INT UNSIGNED; DECLARE tmp_dr_market_id INT UNSIGNED; IF NEW.market_id is null THEN SELECT id INTO tmp_market_id FROM markets WHERE dr_market_id = NEW.dr_market_id ; SET NEW.market_id = tmp_market_id; END IF; END;| delimiter ; The trigger gives no warnings. However when I try to insert a row into area_codes_markets, I get a foreign key constraints error: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`area_codes_markets`, CONSTRAINT `area_codes_markets_ibfk_1` FOREIGN KEY (`market_id`) REFERENCES `markets` (`id`)) The table 'markets' is populated with the correct ids. Does anyone have an idea what I'm doing wrong? If not, is there a way to print out the values of tmp_market_id and NEW.market_id in the trigger that are echo-ed to the console?
View Replies !
|
TRACKER
