RE: converting numeric to date-time?
2014/09/04 08:40 -0700, Jan Steinman From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. MySQL s interpretation of timestamps is already such that not much such code is needed: see Overview of Date and Time Types. If this, 140930, really were September 30, 2014 it would be enough to write SELECT DATE(140930) This also works: SELECT CAST(140930210215 AS DATETIME) One does not need to write apart-tearing code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
From: Jan Steinman j...@ecoreality.org To: mysql@lists.mysql.com Sent: Thursday, 4 September 2014, 16:40 Subject: RE: converting numeric to date-time? From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153)/*your epoch column here*/ I don't think the OP has a Unix timestamp. Really? Looks like a unix epoch to me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
- Original Message - From: Jan Steinman j...@ecoreality.org Subject: RE: converting numeric to date-time? I don't think the OP has a Unix timestamp. OP explicitly says epoch including milliseconds - so it's going to be three digits too long :-) divide by 1000; split off decimal; from_unixtime(epoch) and add a millisecond display. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: converting numeric to date-time?
From: Ed Mierzwa (emierzwa) emier...@micron.com FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ I don't think the OP has a Unix timestamp. The number looks suspeciously like concatenation of date digits, 140930 at the beginning looks like September 30, 2014. If that's the case, you need to write something that will tear it apart. Nobody talks more of free enterprise and competition and of the best man winning than the man who inherited his father's store or farm. -- C. Wright Mills Jan Steinman, EcoReality Co-op -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: converting numeric to date-time?
SET @tz=@@session.time_zone ; SET SESSION time_zone = '+0:00' ; SELECT DATE_FORMAT( FROM_UNIXTIME(1409304102.153) /*your epoch column here*/ ,'%Y-%m-%d %a %H:%i:%s.%f GMT'); SET SESSION time_zone = @tz ; 2014-08-29 Fri 09:21:42.153000 GMT (or) SELECT DATE_FORMAT( FROM_UNIXTIME(1409304102.153) - INTERVAL ( TIMESTAMPDIFF(SECOND,UTC_TIMESTAMP(),NOW()) ) SECOND ,'%Y-%m-%d %a %H:%i:%s.%f GMT') ; 2014-08-29 Fri 09:21:42.153000 GMT -Original Message- From: Philip Amadeo Saeli [mailto:psa...@zorodyne.com] Sent: Monday, September 01, 2014 5:51 PM To: Rajeev Prasad Cc: MYSQL General List Subject: Re: converting numeric to date-time? * Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]: I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: 2014-8-29 Fri 09:21:42: GMT (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev I do not know how to do it directly in MySQL, but if you can dump the table and post-process, this may be helpful on Linux: The date(1) cmd can translate between formats, e.g. (taking the above value), date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N 2014-08-29 Fri 04:21:42.15300 --Phil -- Philip Amadeo Saeli openSUSE, CentOS, RHEL psa...@zorodyne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
converting numeric to date-time?
I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: 2014-8-29 Fri 09:21:42: GMT (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: converting numeric to date-time?
* Rajeev Prasad rp.ne...@yahoo.com [2014-09-01 17:55]: I have a column in a table which is epoch time including milliseconds. e.g. = 1409304102153 now i want to display all fields in the table but this field as: 2014-8-29 Fri 09:21:42: GMT (whatever comes in ) and i am not finding anything on web about how to do that. can anyone help please. ty. Rajeev I do not know how to do it directly in MySQL, but if you can dump the table and post-process, this may be helpful on Linux: The date(1) cmd can translate between formats, e.g. (taking the above value), date -d @1409304102.153 +%Y-%m-%d %a %H:%M:%S.%N 2014-08-29 Fri 04:21:42.15300 --Phil -- Philip Amadeo Saeli openSUSE, CentOS, RHEL psa...@zorodyne.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Deleting of records older than a specific date time
Hi Everyone I have posted this question quite a while back and noticed now that I haven't gotten an answer as yet and this is still on my unresolved list. We have 2 tables which we want to archive data from. We need to be able to delete all data from the 2 tables that is older than the start of yesterday. The process of determining the rows manually is easy enough, however we would like to cut out manual intervention and automate this process. I have played around with trying to create a stored routine / function which is then passed the date as a parameter and then to use this to determine the rows and delete them. - This created 2 problems for me though -- initially I struggled with the date format because it does not want to accept the date format. -- when at last it seemed to accept the date format , it caused the script to not exit upon completion. I am sure there must be a better way to do this than using functions and linux scripts to automate the process, however my experience is failing me in this respect as I am fairly new to the dba scene. I would really appreciate it if someone can assist me in this regard please. Regards Machiel PS someone already suggested me using MySQL scheduled events , however I was not able to figure out how to use it for this functionality though.
Re: Deleting of records older than a specific date time
A simple stored procedure run by the event scheduler at predetermined times will do this quite easily. Can you post the output of SHOW CREATE TABLE for your two tables? John On 23 May 2011 13:15, Machiel Richards machi...@rdc.co.za wrote: Hi Everyone I have posted this question quite a while back and noticed now that I haven't gotten an answer as yet and this is still on my unresolved list. We have 2 tables which we want to archive data from. We need to be able to delete all data from the 2 tables that is older than the start of yesterday. The process of determining the rows manually is easy enough, however we would like to cut out manual intervention and automate this process. I have played around with trying to create a stored routine / function which is then passed the date as a parameter and then to use this to determine the rows and delete them. - This created 2 problems for me though -- initially I struggled with the date format because it does not want to accept the date format. -- when at last it seemed to accept the date format , it caused the script to not exit upon completion. I am sure there must be a better way to do this than using functions and linux scripts to automate the process, however my experience is failing me in this respect as I am fairly new to the dba scene. I would really appreciate it if someone can assist me in this regard please. Regards Machiel PS someone already suggested me using MySQL scheduled events , however I was not able to figure out how to use it for this functionality though. -- John Daisley Certified MySQL 5 Database Administrator Certified MySQL 5 Developer Cognos BI Developer Telephone: +44 (0)7918 621621 Email: john.dais...@butterflysystems.co.uk
Re: Deleting of records older than a specific date time
Quoting Machiel Richards machi...@rdc.co.za: We need to be able to delete all data from the 2 tables that is older than the start of yesterday. Hi, I use this ver simple script to purge data from a syslog DB: #!/usr/local/bin/bash /usr/local/bin/mysql -u syslog -pmypasswd -e 'DELETE FROM SystemEvents WHERE ReceivedAt date_add(current_date, interval -60 day)' Syslog maybe that is useful for you...? cheers Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Displaying date/time
I'm still trying with this. Can anyone else offer any support or examples ? Thanks, Neil On Mon, Mar 8, 2010 at 6:02 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: Hi Thanks for the positive reply. Your stored procedure kind of works but doesn't give me the desired results. However I will base my requirements on this. Thanks again Neil On Mon, Mar 8, 2010 at 2:42 PM, Price, Randall randall.pr...@vt.eduwrote: Here is a MySQL stored procedure that I have used to format the difference between two dates: CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 BEGIN DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT; DECLARE t0 TIMESTAMP; SET yy = TIMESTAMPDIFF(YEAR, dt1, dt2); SET mm = TIMESTAMPDIFF(MONTH, dt1, dt2) MOD 12; SET d0 = TIMESTAMPDIFF(DAY, dt1, dt2); IF yy = 0 OR mm = 0 THEN SET dd = 0; ELSE SET dd = d0 MOD (yy * mm); END IF; SET t0 = TIMESTAMPADD(DAY, d0, dt1); SET t1 = TIME_TO_SEC(TIMEDIFF(dt2, t0)); SET hh = FLOOR(t1 / 3600); SET mi = FLOOR(t1 / 60) - 60 * hh; SET ss = t1 - 3600 * hh - 60 * mi; RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' hours ', mi, ' mins ', ss, ' secs' ); END Maybe this will help. Thanks, Randall Price Senior Programmer Analyst Virginia Tech -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, March 05, 2010 4:15 PM To: Tompkins Neil; prabhat kumar Cc: [MySQL] Subject: RE: Displaying date/time That's probably something best done in your presentation (app) layer. If you must do this in mysql, then you'll probably want to write a stored function. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, March 05, 2010 10:27 AM To: prabhat kumar Cc: [MySQL] Subject: Re: Displaying date/time Hi That is kind of what I'm looking for. However I'd like to be able to display the difference between date 1 and date 2 like 1d 2h 29min ago Thanks Neil On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar aim.prab...@gmail.com wrote: Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=randall.pr...@vt.edu
RE: Displaying date/time
Here is a MySQL stored procedure that I have used to format the difference between two dates: CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 BEGIN DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT; DECLARE t0 TIMESTAMP; SET yy = TIMESTAMPDIFF(YEAR, dt1, dt2); SET mm = TIMESTAMPDIFF(MONTH, dt1, dt2) MOD 12; SET d0 = TIMESTAMPDIFF(DAY, dt1, dt2); IF yy = 0 OR mm = 0 THEN SET dd = 0; ELSE SET dd = d0 MOD (yy * mm); END IF; SET t0 = TIMESTAMPADD(DAY, d0, dt1); SET t1 = TIME_TO_SEC(TIMEDIFF(dt2, t0)); SET hh = FLOOR(t1 / 3600); SET mi = FLOOR(t1 / 60) - 60 * hh; SET ss = t1 - 3600 * hh - 60 * mi; RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' hours ', mi, ' mins ', ss, ' secs' ); END Maybe this will help. Thanks, Randall Price Senior Programmer Analyst Virginia Tech -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, March 05, 2010 4:15 PM To: Tompkins Neil; prabhat kumar Cc: [MySQL] Subject: RE: Displaying date/time That's probably something best done in your presentation (app) layer. If you must do this in mysql, then you'll probably want to write a stored function. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, March 05, 2010 10:27 AM To: prabhat kumar Cc: [MySQL] Subject: Re: Displaying date/time Hi That is kind of what I'm looking for. However I'd like to be able to display the difference between date 1 and date 2 like 1d 2h 29min ago Thanks Neil On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar aim.prab...@gmail.com wrote: Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=randall.pr...@vt.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Displaying date/time
Hi Thanks for the positive reply. Your stored procedure kind of works but doesn't give me the desired results. However I will base my requirements on this. Thanks again Neil On Mon, Mar 8, 2010 at 2:42 PM, Price, Randall randall.pr...@vt.edu wrote: Here is a MySQL stored procedure that I have used to format the difference between two dates: CREATE definer=`ro...@`127.0.0.1` FUNCTION `sp_maint_PeriodLength`(dt1 DATETIME, dt2 DATETIME) RETURNS char(128) CHARSET latin1 BEGIN DECLARE yy, mm, d0, dd, hh, mi, ss, t1 BIGINT; DECLARE t0 TIMESTAMP; SET yy = TIMESTAMPDIFF(YEAR, dt1, dt2); SET mm = TIMESTAMPDIFF(MONTH, dt1, dt2) MOD 12; SET d0 = TIMESTAMPDIFF(DAY, dt1, dt2); IF yy = 0 OR mm = 0 THEN SET dd = 0; ELSE SET dd = d0 MOD (yy * mm); END IF; SET t0 = TIMESTAMPADD(DAY, d0, dt1); SET t1 = TIME_TO_SEC(TIMEDIFF(dt2, t0)); SET hh = FLOOR(t1 / 3600); SET mi = FLOOR(t1 / 60) - 60 * hh; SET ss = t1 - 3600 * hh - 60 * mi; RETURN CONCAT( yy, ' years ', mm, ' months ', dd, ' days ', hh, ' hours ', mi, ' mins ', ss, ' secs' ); END Maybe this will help. Thanks, Randall Price Senior Programmer Analyst Virginia Tech -Original Message- From: Gavin Towey [mailto:gto...@ffn.com] Sent: Friday, March 05, 2010 4:15 PM To: Tompkins Neil; prabhat kumar Cc: [MySQL] Subject: RE: Displaying date/time That's probably something best done in your presentation (app) layer. If you must do this in mysql, then you'll probably want to write a stored function. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, March 05, 2010 10:27 AM To: prabhat kumar Cc: [MySQL] Subject: Re: Displaying date/time Hi That is kind of what I'm looking for. However I'd like to be able to display the difference between date 1 and date 2 like 1d 2h 29min ago Thanks Neil On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar aim.prab...@gmail.com wrote: Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=randall.pr...@vt.edu
Displaying date/time
Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil
Re: Displaying date/time
Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.comwrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Displaying date/time
Hi That is kind of what I'm looking for. However I'd like to be able to display the difference between date 1 and date 2 like 1d 2h 29min ago Thanks Neil On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar aim.prab...@gmail.com wrote: Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
RE: Displaying date/time
That's probably something best done in your presentation (app) layer. If you must do this in mysql, then you'll probably want to write a stored function. -Original Message- From: Tompkins Neil [mailto:neil.tompk...@googlemail.com] Sent: Friday, March 05, 2010 10:27 AM To: prabhat kumar Cc: [MySQL] Subject: Re: Displaying date/time Hi That is kind of what I'm looking for. However I'd like to be able to display the difference between date 1 and date 2 like 1d 2h 29min ago Thanks Neil On Fri, Mar 5, 2010 at 3:32 PM, prabhat kumar aim.prab...@gmail.com wrote: Might be this will help you: there is a table called message with 3 colums - id, pubdate and message; You can get all messages from the last 5 minutes with the following example; SELECT TIMESTAMPDIFF(MINUTE, pubdate, now()), id, message from message where (TIMESTAMPDIFF(MINUTE, pubdate, now()) 5); Thanks, On Fri, Mar 5, 2010 at 8:19 PM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi I have a number of rows which have Date and Time data in. I want to display in the following formats based on the systems current time e.g under 1 hour 24min ago e.g under 1 day 16h 29min ago e.g over 1 day 1d 2h 29min ago e.g over 1 week 1w 4d 2h 29min ago How would this best be achieve using MySQL. Thanks, Neil -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Date Time
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
mysql select * from t;
+-+
| d |
+-+
| 2009-05-21 03:15:28 |
+-+
1 row in set (0.01 sec)
On Thu, 2009-05-21 at 15:19 -0600, John Meyer wrote:
Is Thu May 21 03:15:28 + 2009 a valid date/time string?
--
Best Regards,
-Janek Bogucki, CMDEV 5.0.
StudyLink. Helping People Realise Their Potential.
http://studylink.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
Thanks. That'll work.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Date Time
mysql create table t(d datetime);
Query OK, 0 rows affected (0.08 sec)
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009', '%a
%b %e %H:%i:%s + %Y'));
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
+-+
| d |
+-+
| 2009-05-21 03:15:28 |
+-+
most of us have contacts in europe who dont use EDT,CDT,MDT or PDT so
i *was hoping* to get confirmation on CONVERT_TZ(date,from_tz,to_tz) works?
Thanks,
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est
interdite. Ce message sert à l'information seulement et n'aura pas n'importe
quel effet légalement obligatoire. Étant donné que les email peuvent facilement
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité
pour le contenu fourni.
Date: Fri, 22 May 2009 07:14:58 -0600
From: john.l.me...@gmail.com
To: janek.bogu...@studylink.com
CC: mysql@lists.mysql.com
Subject: Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 +
2009', '%a %b %e %H:%i:%s + %Y'));
Thanks. That'll work.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
_
Hotmail® has ever-growing storage! Don’t worry about storage limits.
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009
Re: Date Time
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009',
'%a %b %e %H:%i:%s + %Y'));
BTW, how would you work that with offsets that were a different value
(say +0700).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Date Time
On Fri, May 22, 2009 at 12:42 PM, John Meyer john.l.me...@gmail.com wrote:
Janek Bogucki wrote:
Hi John,
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.
'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.
mysql create table t(d datetime);
mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 +
2009', '%a %b %e %H:%i:%s + %Y'));
BTW, how would you work that with offsets that were a different value (say
+0700).
I don't think you do. You would have to get your application to parse
out the date into something that MySQL can negotiate. The only reason
this works at all in your case is because you happen to have +
which we can safely ignore.
--
- michael dykman
- mdyk...@gmail.com
- All models are wrong. Some models are useful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Date Time
Is Thu May 21 03:15:28 + 2009 a valid date/time string? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Date Time
mysql select sysdate() from DUAL; +-+ | sysdate() | +-+ | 2009-05-21 17:37:13 | +-+ i would get the proprt format is i could CONVERT_TZ to work can you get CONVERT_TZ to work ? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Thu, 21 May 2009 15:19:16 -0600 From: john.l.me...@gmail.com To: mysql@lists.mysql.com Subject: Date Time Is Thu May 21 03:15:28 + 2009 a valid date/time string? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009
Using MySQL date/time
Hello guys, I would like to get some registers on my database that are older than 90 days, and after delete it because the table is very larger: mysql select count(*) from max_ecardsent; +--+ | count(*) | +--+ | 1172330 | +--+ 1 row in set (0.01 sec) But I'm confusing when I use the FROM_UNIXTIME, the output is very confusable, like: mysql SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \ FROM max_ecardsent ORDER BY cs_date_delete ASC LIMIT 5; +---+---+ | FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) | +---+---+ | 2007-03-05 20:16:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | | 2007-03-05 21:10:56 | 2007-03-20 00:00:00 | +---+---+ mysql SELECT FROM_UNIXTIME(cs_date_create),FROM_UNIXTIME(cs_date_delete) \ FROM max_ecardsent ORDER BY cs_date_delete DESC LIMIT 5; +---+---+ | FROM_UNIXTIME(cs_date_create) | FROM_UNIXTIME(cs_date_delete) | +---+---+ | 2006-06-07 23:55:37 | 2010-01-08 02:00:22 | | 2006-06-07 23:55:37 | 2010-01-08 02:00:22 | | 2006-06-15 15:16:17 | 2009-07-01 03:00:22 | | 2006-05-25 18:26:08 | 2009-06-09 03:00:22 | | 2006-05-25 18:29:59 | 2009-06-09 03:00:22 | +---+---+ 5 rows in set (2.56 sec) Someone can help me? Thanks! mysql desc max_ecardsent; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | cs_id | varchar(20) | NO | PRI | | | | cs_ec_id | mediumint(8) | NO | MUL | 0 | | | cs_message | text | YES | | NULL| | | cs_sent| tinyint(1) | NO | | 0 | | | cs_send_month | tinyint(2) | NO | | 0 | | | cs_send_mday | tinyint(2) | NO | | 0 | | | cs_send_year | mediumint(4) | NO | | 0 | | | cs_notify | tinyint(1) | NO | | 0 | | | cs_copy| tinyint(1) | NO | | 0 | | | cs_fmail | varchar(255) | NO | | | | | cs_fname | varchar(100) | NO | | | | | cs_from_email | varchar(255) | NO | | | | | cs_from_name | varchar(100) | NO | | | | | cs_date_create | int(11) | NO | MUL | 0 | | | cs_date_create_server_time | int(11) | NO | | 0 | | | cs_date_send | int(11) | NO | | 0 | | | cs_date_delete | int(11) | NO | | 0 | | | cs_lang| varchar(50) | YES | | NULL| | | cs_user_name_id| varchar(25) | NO | | | | | cs_pkdate | int(11) | NO | | 0 | | | cs_timezone| decimal(5,2) | NO | | 0.00| | | cs_music_filename | varchar(100) | YES | | NULL| | | cs_poem| varchar(100) | YES | | NULL| | | cs_skin_name | varchar(100) | YES | | NULL| | | cs_stamp_filename | varchar(100) | YES | | NULL| | | cs_java| varchar(50) | YES | | NULL| | | cs_poem_align | varchar(10) | YES | | NULL| | | cs_sender_ip | varchar(15) | YES | MUL | NULL| | ++--+--+-+-+---+ 28 rows in set (0.01 sec) -- Tiago Cruz http://everlinux.com Linux User #282636 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
interesting date/time query issue
I have data that is broken into anything from 30 sec to 15 minute time series (with a DATETIME field). I need to transform all of this into 15 minute data. Does anyone know off the top of their head if there a way I could use GROUP BY to make this happen? Nothing I have tried thus far has worked but it seems as though all of the pieces are there, but there does not appear to be any way to do the comparison in a way that GROUP BY can use it. Otherwise I can write a script to select all of the data and loop over it but you can see why I want to do this in SQL. Thanks, Tom -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting a date/time field to UTC
I have a timestamp stored in a datetime field. It gets set using Now() when the record was inserted. I would like to pull the data back out and have it in UTC time. Is there an easy way to do this? I've been through the manual and on google but haven't come up with anything. This is in a PHP script I'd like other people to be able to use, so I'd like it to easily run on any MySQL 4.1.x system. Maybe it would just be easiest if I did the UTC conversion in PHP? The only other thing I was thinking of is getting the difference between the regular time and UTC_TIME and then add that to the field value. -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Problem with V5.0.6 views
Hello. You said that you had created a view, but you continued using Tab_A instead of Tab_A_View in your next queries. Did you want to use view Tab_A_View? On my MySQL 5.0.9 all queries works both with view and original table. See: mysql desc Tab_A; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | | | | num_field | bigint(20) | NO | | | | +++--+-+-+---+ mysql desc Tab_A_View; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | datetime_field | datetime | NO | | -00-00 00:00:00 | | | num_field | bigint(20) | NO | | 0 | | +++--+-+-+---+ mysql Select datetime_field, num_field from Tab_A_View where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ mysql Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' andnum_field = 1234; +-+---+ | datetime_field | num_field | +-+---+ | 2005-03-10 00:00:00 | 1234 | | 2005-03-10 00:00:00 | 1234 | +-+---+ [EMAIL PROTECTED] wrote: Hello everyone, I cannot figure this out. I have a table like the following: Tab_A datetime_field datetime not null, num_field bigint not null I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; I return 2 rows correctly. I then create the view: Create view Tab_A_View as select * from Tab_A; I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; 0 rows returned! HOWEVER, IF I do the query as such: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10 00:00:00' and num_field = 1234; 2 rows returned correctly. Is there an implementation difference? Regards, George __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date/Time Problem with V5.0.6 views
Hello everyone, I cannot figure this out. I have a table like the following: Tab_A datetime_field datetime not null, num_field bigint not null I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; I return 2 rows correctly. I then create the view: Create view Tab_A_View as select * from Tab_A; I do a SELECT as follows: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10' and num_field = 1234; 0 rows returned! HOWEVER, IF I do the query as such: Select datetime_field, num_field from Tab_A where datetime_field='2005-03-10 00:00:00' and num_field = 1234; 2 rows returned correctly. Is there an implementation difference? Regards, George __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE TIME
Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help hans -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
On Wed, Apr 06, 2005 at 12:06:37PM +0200, Hans Bernard wrote: i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- Use date_format(, %d-%b-%Y %H:%m) in your SELECT? bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
- Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE TIME
thanks for the hints date_format() solved my problem my select query in php is now the following: $query = SELECT id, calltt, date_format(calldate, '%d-%b-%Y') AS calldate2, date_format(calltime, '%H:%i') AS calltime2,area, problem, solution, assignto, status FROM ticketing ; hans Rhino wrote: - Original Message - From: Hans Bernard [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 06, 2005 6:06 AM Subject: DATE TIME Hello, i need to have the time in this output. in MySQL database phpmyadmin always puts -00-00 i need it to be like this 01-APR-2005 DD-MMM- the time needs to be like this 22:55 HH:MM can somebody help I am 99% sure that you cannot actually store dates or times in the formats that you want. However, I am also 99% sure that you *can* retrieve them in those formats by reformatting them when you retrieve them. I think you will find all of the information you need under functions in the MySQL manual. Unfortunately, the server appears to be down at the moment so I can't give you a link to the appropriate page. Also, I suspect the reason that you are seeing dates of '-00-00' is that you are supplying the dates in the wrong format within your INSERT statements and that MySQL is storing '-00-00' as the default. If you supply the dates in the format MySQL is expecting, you should get back the dates that you actually stored. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 05/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Filtering a date/time out of timestamp
I have been trying to figure out a way to filter a date out of timestamp to no success. How do I filter out specific critieria that I need using now()? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Filtering a date/time out of timestamp
[snip] I have been trying to figure out a way to filter a date out of timestamp to no success. How do I filter out specific critieria that I need using now()? [/snip] http://www.mysql.com/substring WHERE now() = substring(dateTimeColumnName, 1, 8) should get it -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Filtering a date/time out of timestamp
Thanks to both of you, here is my string: select * from test where date(now())=substring(date,1,10); :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date time functions don't return not null rows
Your tables aren't setup very well. You should google for normalization and 'boyce codd normal form' -Eric On Sat, 16 Oct 2004 22:27:51 +0200, owca [EMAIL PROTECTED] wrote: I'm trying get current week, starting from monday to sunday: select UNIX_TIMESTAMP(day), g15, g16, g17, g18, g19, g20, g21, g22, id from tydzien where to_days(day) between to_days(now())- mod(to_days(now()),7)+2 /*some parameter setting a starting day*/ and (to_days(now())- mod(to_days(now()),7))+8 /*ending day ^*/ order by dzien; it works! the table looks like this: ++--+--+--+--+--+--+--+--++ | day| g15 | g16 | g17 | g18 | g19 | g20 | g21 | g22 | id | ++--+--+--+--+--+--+--+--++ | 1098128298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 5 | | 1098473898 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 9 | | 1098560298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 10 | | 1098646698 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 | ++--+--+--+--+--+--+--+--++ but it won't return the rows where any of columns g15, g16 etc has a value. eg ++--+--+--+--+--+--+--+--++ | day| g15 | g16 | g17 | g18 | g19 | g20 | g21 | g22 | id | ++--+--+--+--+--+--+--+--++ | 1098128298 | NULL | 5561 | NULL | NULL | NULL | NULL | NULL | NULL | 9 | | 1098473898 | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 10 | | 1098560298 | 2355 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 | ++--+--+--+--+--+--+--+--++ did i something wrong? -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date time functions don't return not null rows
I'm trying get current week, starting from monday to sunday: select UNIX_TIMESTAMP(day), g15, g16, g17, g18, g19, g20, g21, g22, id from tydzien where to_days(day) between to_days(now())- mod(to_days(now()),7)+2 /*some parameter setting a starting day*/ and (to_days(now())- mod(to_days(now()),7))+8 /*ending day ^*/ order by dzien; it works! the table looks like this: ++--+--+--+--+--+--+--+--++ | day| g15 | g16 | g17 | g18 | g19 | g20 | g21 | g22 | id | ++--+--+--+--+--+--+--+--++ | 1098128298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 5 | | 1098473898 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 9 | | 1098560298 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 10 | | 1098646698 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 | ++--+--+--+--+--+--+--+--++ but it won't return the rows where any of columns g15, g16 etc has a value. eg ++--+--+--+--+--+--+--+--++ | day| g15 | g16 | g17 | g18 | g19 | g20 | g21 | g22 | id | ++--+--+--+--+--+--+--+--++ | 1098128298 | NULL | 5561 | NULL | NULL | NULL | NULL | NULL | NULL | 9 | | 1098473898 | NULL | NULL | NULL | | NULL | NULL | NULL | NULL | 10 | | 1098560298 | 2355 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 11 | ++--+--+--+--+--+--+--+--++ did i something wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date/Time Difference Calculations
I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start)); Dirk Bremer (NISC) wrote: Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:57 PM Subject: Date/Time Difference Calculations I'm using MySQL version 4.0.18. I have two datetime columns in the same table, one that represents a start time and the other that represents an end time. I would like to write a query that will show the difference between these two columns in a HH:MM:SS format. The values of the two columns as inserted into the table are never be separated by more than a few hours, but could span a day boundary, i.e the start time could be late one day end the end time early the next day, so the date will have to be taken into consideration for the calculation. Looking at the docs, it appears that version 4.1.x has a lot more date/time functions, but I'm wondering if something similar can be arrived at under the version that I am using. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
Close, but time_to_sec requires a time argument, not a datetime argument. My next iteration is: select ident, transfer_start, transfer_end, sec_to_time(time_to_sec(substring(transfer_end,12,8)) - time_to_sec(substring(tra cast(transfer_end - transfer_start as signed) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 02:29:16 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:01:56 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:09:10 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | -23:11:07 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:00:52 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 00:20:34 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:00:35 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:20:14 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.01 sec) Which appears to be working correctly (Transfer Time1) except for ident = 1309, which spans a date boundary. So, back to the drawing board to figure out the date boundary issue. Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:50 Subject: Re: Date/Time Difference Calculations select sec_to_time(time_to_sec(transfer_end)-time_to_sec(transfer_start)); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date/Time Difference Calculations
I didn't see where these were 4.1+ function so I think it will work. I refer you to: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into 'negative' time differences) SELECT sec_to_time(unix_timestamp(transfer_end) - unix_timestamp(transfer_start)) from queue; I know it will work for values up to 24 hours different. If sec_to_time is using an internal TIME data type value you get just under 840 hours of differential. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dirk Bremer \(NISC\)To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: .cc Fax to: Subject: Re: Date/Time Difference Calculations 06/11/2004 03:37 PM Looking at the results further reveals that a numeric subtraction is being performed on the two datetime fields rather than a date-type subtraction. Any thoughts on how to perform a date subtraction in version 4.0.18? Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:29 Subject: Re: Date/Time Difference Calculations Here is an example using sec_to_time. Note that the results are inconsistent and sometimes inaccurate. It seems that when the difference is less than one minute, the result is correct, when it is over one minute, the result is incorrect. select ident, transfer_start, transfer_end, sec_to_time(transfer_end - transfer_start) as 'Transfer Time1', (transfer_end - transfer_start) as 'Transfer Time2' from queue where ident 1300 order by queue_time -- +---+-+-++-- --+ | ident | transfer_start | transfer_end| Transfer Time1 | Transfer Time2 | +---+-+-++-- --+ | 1301 | 2004-06-10 09:32:26 | 2004-06-10 09:32:28 | 00:00:02 | 2 | | 1302 | 2004-06-10 09:33:26 | 2004-06-10 09:33:29 | 00:00:03 | 3 | | 1303 | 2004-06-10 13:00:38 | 2004-06-10 15:29:54 | 06:21:56 | 22916 | | 1304 | 2004-06-10 13:17:35 | 2004-06-10 13:19:31 | 00:03:16 | 196 | | 1305 | 2004-06-10 13:19:35 | 2004-06-10 13:19:37 | 00:00:02 | 2 | | 1306 | 2004-06-10 13:20:35 | 2004-06-10 13:20:38 | 00:00:03 | 3 | | 1307 | 2004-06-10 19:37:50 | 2004-06-10 19:47:00 | 00:15:50 | 950 | | 1308 | 2004-06-10 23:10:08 | 2004-06-10 23:10:23 | 00:00:15 | 15 | | 1309 | 2004-06-10 23:11:08 | 2004-06-11 00:00:01 | 213:34:53 | 768893 | | 1311 | 2004-06-10 23:07:08 | 2004-06-10 23:08:00 | 00:01:32 | 92 | | 1310 | 2004-06-10 23:08:58 | 2004-06-10 23:08:58 | 00:00:00 | 0 | | 1312 | 2004-06-11 08:58:30 | 2004-06-11 09:19:04 | 01:41:14 | 6074 | | 1315 | 2004-06-11 09:01:30 | 2004-06-11 09:02:05 | 00:01:15 | 75 | | 1313 | 2004-06-11 09:03:30 | 2004-06-11 09:23:44 | 00:33:34 | 2014 | | 1314 | 2004-06-11 09:24:32 | 2004-06-11 09:24:45 | 00:00:13 | 13 | +---+-+-++-- --+ 15 rows in set (0.00 sec) Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc You probably want SEC_TO_TIME: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html SELECT a as start, b as end, SEC_TO_TIME(end - start) FROM table Eamon Daly - Original Message - From: Dirk Bremer (NISC) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, June 11
Re: Date/Time Difference Calculations
Bingo, Shawn wins the virtual beer(s). I never looked at the unix_timestamp function. Thanks to everyone else and wishing you virtual beers as well! Dirk Bremer - Systems Programmer II - ESS/AMS - NISC St. Peters USA Central Time Zone 636-922-9158 ext. 8652 fax 636-447-4471 [EMAIL PROTECTED] www.nisc.cc - Original Message - From: [EMAIL PROTECTED] To: Dirk Bremer (NISC) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, June 11, 2004 14:54 Subject: Re: Date/Time Difference Calculations I didn't see where these were 4.1+ function so I think it will work. I refer you to: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html http://dev.mysql.com/doc/mysql/en/Cast_Functions.html (if you ever run into 'negative' time differences) SELECT sec_to_time(unix_timestamp(transfer_end) - unix_timestamp(transfer_start)) from queue; I know it will work for values up to 24 hours different. If sec_to_time is using an internal TIME data type value you get just under 840 hours of differential. HTH, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting Date Time field, difference in minutes
C.F. Scheidecker Antunes [EMAIL PROTECTED] wrote: I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)). I wonder if I could do the same with minutes. DATE type doesn't have time part. Use DATETIME instead. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting Date Time field, difference in minutes
Hello all, I wonder if is there any way to select from a table all the records which has a Date Field that is at least five minutes old? In other words, I have a table with a date field and I need to select all the records that are older than five minutes, has their date field updated before five minutes. Usually I do it with days by using the to_days() function and comparing the date to now: (to_dayss(now()) - to_days(somedate)). I wonder if I could do the same with minutes. Thanks in advance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - How can I insert new data with the current date/time?
*if you create a column of the type TIMESTAMP instead of DATETIME then it automatically stores the time when the record was inserted/last updated. If you have more that one column of type TIMESTAMP in the same table then only the first TIMESTAMP column gets updated automatically. (Don't confuse MySQL TIMESTAMP column type with Unix Timestamp, MySQL TIMESTAMP column is just an automatically updated DATETIME column) *to insert current datetime in a DATETIME column, use NOW() or SYSDATE(). e.g. INSERT INTO bug_master VALUES (1, Timothy, NOW()) regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, March 15, 2003 20:08 To: [EMAIL PROTECTED] Subject: Newbie - How can I insert new data with the current date/time? I have a table called animals (using the example in the MySQL guide). I have a datetime column type. Here is the table: create table bug_master ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, date DATETIME NOT NULL, PRIMARY KEY (id) ); I want to do a simple insert into the table with the current date and time, i.e. the system date. I'm having some problems finding the correct sql syntax to do this. Could anyone help with this? Do I need a Now() function? insert into animals the rest of the query??; Thanks! Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie - How can I insert new data with the current date/time?
Thanks Paul, that works great. Yes, I meant to say the table animals, not bug_master. Paul DuBois [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] t cc: Subject: Re: Newbie - How can I insert new data with the current 03/15/2003date/time? 10:37 AM At 8:37 -0600 3/15/03, [EMAIL PROTECTED] wrote: I have a table called animals (using the example in the MySQL guide). I have a datetime column type. Here is the table: Looks like it's called bug_master, not animals. :-) create table bug_master ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, date DATETIME NOT NULL, PRIMARY KEY (id) ); I want to do a simple insert into the table with the current date and time, i.e. the system date. I'm having some problems finding the correct sql syntax to do this. Could anyone help with this? Do I need a Now() function? That's exactly right. insert into animals the rest of the query??; INSERT INTO bug_master (id,name,date) VALUES(id_val,name_val,NOW()); Thanks! Kevin -- Paul DuBois http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Newbie - How can I insert new data with the current date/time?
Great, this is more of what I was looking fora way to have the DB auto populate the date/time in a column, without having to use sql. Thanks again! Uttam [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] in cc: Subject: RE: Newbie - How can I insert new data with the current 03/16/2003date/time? 02:10 AM *if you create a column of the type TIMESTAMP instead of DATETIME then it automatically stores the time when the record was inserted/last updated. If you have more that one column of type TIMESTAMP in the same table then only the first TIMESTAMP column gets updated automatically. (Don't confuse MySQL TIMESTAMP column type with Unix Timestamp, MySQL TIMESTAMP column is just an automatically updated DATETIME column) *to insert current datetime in a DATETIME column, use NOW() or SYSDATE(). e.g. INSERT INTO bug_master VALUES (1, Timothy, NOW()) regards, -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Saturday, March 15, 2003 20:08 To: [EMAIL PROTECTED] Subject: Newbie - How can I insert new data with the current date/time? I have a table called animals (using the example in the MySQL guide). I have a datetime column type. Here is the table: create table bug_master ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, date DATETIME NOT NULL, PRIMARY KEY (id) ); I want to do a simple insert into the table with the current date and time, i.e. the system date. I'm having some problems finding the correct sql syntax to do this. Could anyone help with this? Do I need a Now() function? insert into animals the rest of the query??; Thanks! Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie - How can I insert new data with the current date/time?
I have a table called animals (using the example in the MySQL guide). I have a datetime column type. Here is the table: create table bug_master ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, date DATETIME NOT NULL, PRIMARY KEY (id) ); I want to do a simple insert into the table with the current date and time, i.e. the system date. I'm having some problems finding the correct sql syntax to do this. Could anyone help with this? Do I need a Now() function? insert into animals the rest of the query??; Thanks! Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie - How can I insert new data with the current date/time?
At 8:37 -0600 3/15/03, [EMAIL PROTECTED] wrote: I have a table called animals (using the example in the MySQL guide). I have a datetime column type. Here is the table: Looks like it's called bug_master, not animals. :-) create table bug_master ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, date DATETIME NOT NULL, PRIMARY KEY (id) ); I want to do a simple insert into the table with the current date and time, i.e. the system date. I'm having some problems finding the correct sql syntax to do this. Could anyone help with this? Do I need a Now() function? That's exactly right. insert into animals the rest of the query??; INSERT INTO bug_master (id,name,date) VALUES(id_val,name_val,NOW()); Thanks! Kevin -- Paul DuBois http://www.kitebird.com/ sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql and Import into MS Access with Date Time
Hi all,
It seems that MS Access can't find out how to convert the mysql Time Type
in a table.
Lets say i got 3 colums in a table like this:
create table test (id int(4)auto_increment,dato date,tid time, primary key
(id));
insert into test values ('',curdate(),curtime());
insert into test values ('',curdate(),curtime());
insert into test values ('',curdate(),curtime());
If i do a select * from test then 3 rows occur (offcourse)
mysql select * from test;
+++--+
| id | dato | tid |
+++--+
| 1 | 2003-02-26 | 22:50:30 |
| 2 | 2003-02-26 | 22:50:33 |
| 3 | 2003-02-26 | 22:50:34 |
+++--+
3 rows in set (0.00 sec)
Now to the Strange part.
If i use MS Access and do a import or a Linked tables (sorry cant remember
what is called excatly), then this occur.
id dato tid
1 26-02-2003 30-01-2026 23:52:00
2 26-02-2003 30-01-2026 23:52:00
3 26-02-2003 30-01-2026 23:52:00
If i change the column tid in the inputmask in MS Access HH:MM:SS it only
change to
id dato tid
1 26-02-2003 23:52:00
2 26-02-2003 23:52:00
3 26-02-2003 23:52:00
Why?
Can someone tell me what to do to get this to work??? And i don't wan't to
use another date or timeformat.
Regards
Frank
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: default date time insertion in the database
Daya, Thursday, September 26, 2002, 7:53:46 AM, you wrote: DKD can anyone tell me how can i set the default datetime as current datetime in DKD the mysql. so that every time i insert a record in the table current DKD datetime automaticall inserted. DKD Thanks in advance. Take a look at TIMESTAMP column type: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: default date time insertion in the database
Hi,
can anyone tell me how can i set the default datetime as current
datetime in the mysql. so that every time i insert a record in the table
current datetime automaticall inserted.
Thanks in advance.
From MySQL manual section 6.5.3:
Default values must be constants. This means, for example, that you
cannot set the default for a date column to be the value of a function
such as NOW() or CURRENT_DATE.
You have a couple options though, you can use a TIMESTAMP field which will
be set to the current date/time when the data is inserted. But if the
data changes, the timestamp field is updated again, so this will only work
if you can be certain that you will not be updating the data after
insertion.
The other option is to use a DATETIME type column and use the following
insert query:
INSERT INTO mytable (col1, col2, ... , date_inserted) VALUES ('col1data',
'col2data', ... , NOW());
Josh
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
default date time insertion in the database
Hi, can anyone tell me how can i set the default datetime as current datetime in the mysql. so that every time i insert a record in the table current datetime automaticall inserted. Thanks in advance. Regards Daya Krishan Dubey Core Solucomm Ltd 423 B, Hamilton court DLF phase IV Gurgaon, India Ph# 91-124-6392896/7 (O) Mobile# 91-9811294209 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem with date/time column
hi, i have a date/time column. i insert into it using now(). works fine. now i want a query where data/time is either today or yesterday. how should i do it? thanks/erick __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with date/time column
its select date_sub(now(),INTERVAL 1 DAY) - Original Message - From: Erick Papadakis [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 1:25 PM Subject: problem with date/time column hi, i have a date/time column. i insert into it using now(). works fine. now i want a query where data/time is either today or yesterday. how should i do it? thanks/erick __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with date/time column
Hi Erick, i have a date/time column. i insert into it using now(). works fine. now i want a query where data/time is either today or yesterday. how should i do it? Calculate the date required (yesterday and/or today) and then add a time component of midnight (all zeroes) to make up a date-time value. Then it is a matter of performing a WHERE calculated_time column_value. If you are planning on performing date arithmetic you may want to consider moving from date-time format to UNIX format timestamps. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with date/time column
Instead why don't you try select date_sub(now(), INTERVAL 1 DAY) Karthik. - Original Message - From: DL Neil [EMAIL PROTECTED] To: Erick Papadakis [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 3:09 PM Subject: Re: problem with date/time column Hi Erick, i have a date/time column. i insert into it using now(). works fine. now i want a query where data/time is either today or yesterday. how should i do it? Calculate the date required (yesterday and/or today) and then add a time component of midnight (all zeroes) to make up a date-time value. Then it is a matter of performing a WHERE calculated_time column_value. If you are planning on performing date arithmetic you may want to consider moving from date-time format to UNIX format timestamps. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with date/time column
Karthik, Instead why don't you try select date_sub(now(), INTERVAL 1 DAY) You suggested this earlier. Are you simply repeating the comment or asking me (cf the original poster) a question? Herewith an assumption that you want to maximise your learning. Obviously I can't answer for Erick. However his statement of requirement is/was where data/time is either today or yesterday. So let's try to understand some terms/specficiations... Take yesterday. To me (in British Summer Time TZ), yesterday began at midnight of 3September, and finished 24-hours later (others can argue the seconds!) - and apologies to anyone for whom my 'today' is already their 'yesterday'. Thus in SQL the term yesterday becomes a BETWEEN comparison. Today could also be considered a BETWEEN, but if all datetimes are in the past, it's not necessary to mention tonight's midnight or even NOW() - but if future dates are included, then BETWEEN it must be. Now let's examine DATE-SUB(). If you'll permit the use of my TZ, here's what it looks like: mysql select now() as MyTime, date_sub(now(), INTERVAL 1 DAY) as TimeBefore; +-+-+ | MyTime | TimeBefore | +-+-+ | 2002-09-04 12:11:32 | 2002-09-03 12:11:32 | +-+-+ 1 row in set (0.01 sec) As you can see, anything that happened during yesterday morning would NOT be included in such an expression. It WILL pick up anything that happened during the last one day/24-hours - both yesterday's and today's 'hours'! Q: Which is 'right'? A: Depends which suits Erick's requirement! Regards, =dn - Original Message - From: DL Neil [EMAIL PROTECTED] To: Erick Papadakis [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Wednesday, September 04, 2002 3:09 PM Subject: Re: problem with date/time column Hi Erick, i have a date/time column. i insert into it using now(). works fine. now i want a query where data/time is either today or yesterday. how should i do it? Calculate the date required (yesterday and/or today) and then add a time component of midnight (all zeroes) to make up a date-time value. Then it is a matter of performing a WHERE calculated_time column_value. If you are planning on performing date arithmetic you may want to consider moving from date-time format to UNIX format timestamps. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem with date/time column
Erick, Wednesday, September 04, 2002, 10:55:55 AM, you wrote: EP i have a date/time column. i insert into it using now(). works fine. EP now i want a query where data/time is either today or yesterday. EP how should i do it? Take a look at DATE and TIME functions such as CURDATE(), DATE_SUB(): http://www.mysql.com/doc/en/Date_and_time_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Subsecond date/time values?
How are people representing subsecond date/time values with MySQL? In Sybase, the datetime data type supports subsecond values with a resolution of 1/300th of a second (go figure). PostgreSQL does microseconds with its time (4 bytes) and timestamp (8 bytes) data types. More generally, JDBC provides java.sql.Timestamp which can represent nanoseconds (though I haven't found a database that supports nanoseconds yet). I would prefer to be able to use a single column to represent date + time with some reasonable subsecond granularity. I could use bigint since Java uses 64-bit values to represent timestamps, but then I lose any useful date manipulation facilities in SQL. Thanks, ~chuck IMPORTANT NOTICES: This message is intended only for the addressee. Please notify the sender by email if you are not the intended recipient. If you are not the intended recipient, you may not copy, disclose, or distribute this message or its content to any other person and any such actions may be unlawful. Electronic mail sent through the Internet is not secure. WR Hambrecht + Co (WRH+Co) does not accept time sensitive, action-oriented messages or transaction orders, including orders to purchase or sell securities, via email. WRH+Co reserves the right to monitor and review the content of all messages sent to or from this email address. Messages sent to or from this email address may be stored on the WRH+Co email system. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to add date/time to reports?
I was just wondering that how to add date/time to every report run in MySql. I've got a database that contains everything about my home-made beers. I run a select-query sometimes and I want to include the time when it was run in it. So, any ideas? Thanks! _ Chat with friends online, try MSN Messenger: http://messenger.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to add date/time to reports?
* Jarkko Toivonen I was just wondering that how to add date/time to every report run in MySql. I've got a database that contains everything about my home-made beers. I run a select-query sometimes and I want to include the time when it was run in it. Add now() as report_time to your field list: SELECT *,now() as report_time FROM table WHERE field='A'; The timestamp will be available as a field named 'report_time'. Put this in the header (or footer) of your report. -- Roger query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Selecting boundary dates for a given week using date/time functions?
Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/time functions?
Well, whatever language you are using to output your data, there should be some unix tampstamp converting function. In php it's date(). On Sunday 25 November 2001 04:20 pm, Chris Cooper wrote: Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Selecting boundary dates for a given week using date/time functions?
Hi Chris, The following query should work for you. select date_sub(date_col, interval weekday(date_col) day), date_add(date_col, interval 6-weekday(date_col) day) from table_name Anvar. At 07:20 PM 25/11/2001 -0500, you wrote: Hey all, I'm hoping someone can help me out with a little date/time problem I'm having. I've got a database full of timestamps that I want to group together based on their week. I can use the WEEK() function or DATE_FORMAT() to convert the timestamp to a week number, but I'd rather display the boundary dates for the week because I don't feel the week number is very informative. e.g. Given the timestamp '2004095959' (a Wednesday), I would like to output '2001-11-12 to 2001-11-18' (which is Monday to Sunday). Is there a way using the date/time functions of MySQL to make this conversion in my SELECT statement? I haven't been able to figure out a way to get MySQL to use a week number to generate these boundary dates. I'm assuming the final SQL will look something like: SELECT CONCAT(function_to_get_monday, ' to ', function_to_get_sunday)... Any help is appreciated. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date/time advice and stuff
Hi there. I need advice on which is better to store date/time information on a database and retrieve data between two given dates. It appears to be pretty simple to use timestamp, which value is always bigger as time goes on, and I could compare using logical operators in a SQL query. Is this tought correct? By the way, is there a function or somewhat that results in the 'somatory' of all values in a given interval? Thank you. --- Conrado Vardanega [EMAIL PROTECTED] http://go.to/conrado - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
extract day and hours/minutes from a date-time field but ugly results... HELP!
i want to extract day and hours/minutes from a date-time field. in order to to this i use e.g. for the hours: select HOUR(hora) as lahora from mytable which returns the hour but followed by an L, same for minutes, same for the date. why is this so and how can i avoid it? we run mySQL under Zope. thanks, tim database sql query -- Sent through GMX FreeMail - http://www.gmx.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date,time,datetime type
Hi there, If I need compare 2 fields of DATE and TIME to a field of TIMESTAMP/DATETIME in my query, what am I supposed to do? Is there a function to get DATE/TIME part from a DATETIME field, or combine DATE and TIME to a DATETIME type? thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date,time,datetime type
Ch 7 of the manual contains a list of date functions that allows you to do everything, short of boiling an egg. - Original Message - From: "Kevin Xin Lin" [EMAIL PROTECTED] To: "mysql" [EMAIL PROTECTED] Sent: Wednesday, April 18, 2001 22:10 Subject: Date,time,datetime type Hi there, If I need compare 2 fields of DATE and TIME to a field of TIMESTAMP/DATETIME in my query, what am I supposed to do? Is there a function to get DATE/TIME part from a DATETIME field, or combine DATE and TIME to a DATETIME type? thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Date/Time difference
Hello, I'm reading this list for some time and checked the FAQ, archive but i haven't found any hints ... I have an own written app which is using a 'kind of database' to calculate the difference between two (or more) events which can happen between tens or hundreds of seconds. Now i want to switch to a real database but i'm unable to find a database function to calculate the difference between (for example '2001-03-05- 20:09:10.54' and '2001-03-05 20:11:34.36') of two events with a 'factional' time. I've tried something like INSERT INTO t_start, t_stop VALUES (UNIX_TIMESTAP(now() + 0.54) , UNIX_TIMESTAMP(now() + 0.38) but this is not working for a 'DATETIME' field, a 'FLOAT' field returns 0. The difference between the two times is very critical because the app is written for sports events ... Maybe someone uses MySQL within such an application and has can provide some hints Andreas Karl Wittwer Phone: +49-7052-92206 FAX: +49-7052-92208 Mobil: +49-172-542 541 4 [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Date/Time difference
Hi,
Your main problem will be in storage - datetime fields are only down to the
second, so you will have to store the times as integers, and then do your
own arithmetic on them.
If you stored the time as ('unix time' * 100) + hundreths, then you could
probably still do some arithmetic using the standard functions and
'col_name/100', but that will only be accurate to a second.
Regards
Quentin
-Original Message-
From: Andreas Karl Wittwer [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 6 March 2001 2:54 p.m.
To: [EMAIL PROTECTED]
Subject: Date/Time difference
Hello,
I'm reading this list for some time and checked the FAQ, archive
but i haven't found any hints ...
I have an own written app which is using a 'kind of database' to
calculate the difference between two (or more) events which can
happen between tens or hundreds of seconds.
Now i want to switch to a real database but i'm unable to
find a database function to calculate the difference between (for
example '2001-03-05- 20:09:10.54' and '2001-03-05 20:11:34.36')
of two events with a 'factional' time.
I've tried something like
INSERT INTO t_start, t_stop VALUES
(UNIX_TIMESTAP(now() + 0.54) , UNIX_TIMESTAMP(now() + 0.38)
but this is not working for a 'DATETIME' field, a 'FLOAT' field
returns 0.
The difference between the two times is very critical because the
app is written for sports events ...
Maybe someone uses MySQL within such an application and has can
provide some hints
Andreas Karl Wittwer
Phone: +49-7052-92206
FAX: +49-7052-92208
Mobil: +49-172-542 541 4
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
The information contained in this email is privileged and confidential
and intended for the addressee only. If you are not the intended
recipient, you are asked to respect that confidentiality and not
disclose, copy or make use of its contents. If received in error
you are asked to destroy this email and contact the sender immediately.
Your assistance is appreciated.
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
