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 Complete Forum Thread with Replies
Related Forum Messages:
Convert Int Date To Datetime
I have a table that unfortunatley has a field 'DateSold' with datatype of int that SHOULD be datetime. So, I am trying to do a cast/convert. The int is stored as 20040520 (which means 5/20/2004) I tried saying Select col1 from table where cast(DateSold as DATETIME)[color=blue] > '1/1/2004'[/color] but of course it bombs out. What is the most efficient way to do this? Is there a way to cast it without doing some crazy string manipulation?
View Replies !
Convert From Bigint To Datetime
Due to some reason, i store the java.util.Date as milliseconds using the field type BIGINT. However, while i export the record as a csv file, is MYSQL provided any command to convert the milliseconds to datetime format ?
View Replies !
Convert DateTime To UnixTime
i have a database to insert into another database but the form of the timestamp are different old database was in datetime (0000-00-00 00:00:00) new database was in unixtime (1123658974) Is it possible to convert datetime to unixtime directly under mysql (console or phpmyadmin)? If it's yes, what is the command to execute?
View Replies !
Convert Unix_Timestamp To DateTime In A Whole Column
I have an old table with a column: datum varchar(30) with Unix Timestamps in it. Now I want to convert it into a new column with DateTime (0000-00-00 00:00:00). Only have found the syntax to make it the other direction UPDATE tabelle SET tmpDate = UNIX_TIMESTAMP(DATE_FORMAT(myDateTime,'%Y-%m-%d %T')) WHERE myDateTime IS NOT NULL Please can you give me the syntax to convert the whole column from Unix Timestamp to DateTime without loosing my dates.
View Replies !
Convert Function
How can I convert a function (SQL SERVER) that returns a TABLE type and IS USED in queries, like, SELECT * FROM Item INNER JOIN myFunction(SomeParameter) ON Item.ItemID=myFunction.ItemID Here's an example (SQL SERVER): CREATE FUNCTION [DPVIEW71_0](@TransDocument nvarchar(255),@TransSerial nvarchar(255)) RETURNS TABLE AS Return (SELECT [TransDocNumber], [CreateDate], [DeferredPaymentDate], [PartyID], [PartyName], [TotalNetAmount], [TotalTaxAmount], [TotalTransactionAmount], [CurrencyID], [ContractReferenceNumber] FROM [BuyTransaction] WHERE [BuyTransaction].[TransDocument]=@TransDocument AND [BuyTransaction].[TransSerial]=@TransSerial)
View Replies !
Automatically Convert A Column Of Unix Timestamps Into DateTime Values
I have a database with a table called "users" and this lists all users who have registered on the site. within the table, there is a column called "lastlogdate". This contains Unix timestamps (I think they're unix timestamps) showing the times the users last logged in. I would like to convert these values in a new column which will show them as actual dates, hence more recognisable. here's a screenshot of the column:
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 !
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 !
Convert Time To Decimal
I've a time format to convert in decimal, so: 5:41 to decimal is equal to 5,6833. i've looked in the manual and i haven't founded anything. Is there a function for this convertion?
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 !
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 !
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 !
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 !
Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to get a resultset of previous and current and next record when the resultset is sorted by id and id=10. The table data is shown in the following: id Name Price 1 : : 5 : :<--------------previous record 10 : :<--------------id=10 12 : :<--------------next record 14 : : How do I write the sql statement if I do not know 5 and 12 are the previous and next id respectively?
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 !
SEC To TIME Function
I am presently trying to calculating times from a mysql database. I have calculated hrs worked on a job by an employee and hrs worked by an subcontractor using the TIME_T_SEC and SEC_TO_TIME functions. These results I get are correct. When I calculate hrs worked by both and convert to time using the SEC_TO_TIME function I get a wrong result. I have tried a number of ways to do this and get the same wrong result. If I convert both the correct results to seconds and divide by 3600 and add them I get a correct result. I don't understand why the sec_to_time function gives me a wrong result.
View Replies !
Date/time Function
I need to write a query which looks for certain criteria, then returns all rows which occur 0-30 seconds after. Can you please help with a generic query to accomplish this?
View Replies !
SUM() Sort Of Function For TIME Fields?
I have a field in the database that is of the type 'TIME' which I use to track the elapsed time on a task. Lets say I have 4 records in the database where the TIME field has the values: 00:15:00 00:15:00 00:15:00 00:15:00 As you can see if you add up all of these you get an hour. I want to do a query such as: PHP $sql="SELECT SUM(myTimeField) as totalTime FROM myTable WHERE userId=Ƈ'"; And in the result, the value of 'totalTime' in this query should be like: ༽:00:00'.
View Replies !
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 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 !
IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere! The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over £300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....
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 !
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 !
Convert .csv To .sql
I have the data in an .csv file and I need to import it into the MySQL database. How can I do this? I have tried various MySQL clients but they all return errors.
View Replies !
Convert Database
We use the MS access database, I want to convert the database to mysql db , could suggest is there any tools / methods that I can export the table and data from access , and import it to the new mysql db ?
View Replies !
Convert DBF2MYSQL
I have tried to find a tool to convert DBF files to either a MySQL dump or entered directly into tables in my database.Ideally I want to run this export/update process every hour so this program needs to be able to be run by another application, maybe a cron job in Linux.Can anybody recommend a method of approaching this? I have attempted to use, DBF2MYSQL from the Debian packages but I get an error trying to run it, it tells me that the error is/ Code: Header value 0b not valid Couldn't open xbase-file
View Replies !
Convert MySQL To DBF
I would like to know if it possible to convert MySQL to DBF? I have managed to convert MySQL to DBF by using DBF2CSV to create the CSV and then using mysqlimport to get the data into a MySQL table. Has anybody used mysql2dbf successfully because I have recently tried running this program on Debian but I get this error message: "Segmentation fault" The program creates my DBF file but just doesn't fill it with any data, any ideas on this one at all?
View Replies !
Convert String
I have a database with records that contain a string date field in the following format: 22 jan 2005 15:02:22 GMT Any way I can have MySQL format that to a timestamp or other date format which I can use to calculate with? (need to flush records older then a certain date)
View Replies !
Convert SQL Query
I'm trying to convert the following query to MySQL: CAST(CAST(dataMonth AS VARCHAR) + '/' + '01' + '/' + CAST(dataYear AS VARCHAR) AS SMALLDATETIME) BETWEEN '2005/01/01' AND '2005/08/01' AND InputMethod = 1; What this query is doing is taking two fields and stringing them together to form a date. The end result is used to check w/in the given date range. is the SMALLDATETIME considered a column alias when using a CAST function in MySQL? If so, then I can't use the alias name in the WHERE clause of a SELECT statement. Which is what is hanging me up. I'm just trying to take the data from two different fields and convert it to a date so I can use the result in the WHERE clause.
View Replies !
How To Convert *.ddl To *.sql File
I am using MySQL database server for my project but i have database file in form of *.ddl...but to run following mysql dump command, i need *.sql file... mysql dbname -uroot -ppass < test.sql Is it possible to convert *.ddl file into *.sql file...if possible can anyone give me an idea how to convert *.ddl file into *.sql file...then i can use it easily...I tried by saving this ddl file with sql extension...but when i run mysql dump command it failed because some of the statements in that ddl file are not supported by MySQL..some of those are CONNECT TO RXDATABASE; CREATE SCHEMA GENRXDB; SET SCHEMA=GENRXDB;
View Replies !
Convert M9M 2S4 To M9M2S4
I am trying to change postal code data in my mysql table. Current format is M9M 2S4 I would like it to be M9M2S4 I am just a user and not really a programmer or DB guy. I know how to run SQL queries on the table.
View Replies !
|
TRACKER
