Date and Time
Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? Thanks, Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? Andy On Sun, Jan 8, 2012 at 8:21 PM, Donovan Brooke li...@euca.us wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/**refman/5.1/en/date-and-time-**literals.htmlhttp://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? Thanks, Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On 1/8/2012 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks, Donovan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
Peter Brawley wrote: On 1/8/2012 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? As the manual says, MySQL wants -mm-dd. Use Str_To_Date() to format date strings to the format MySQL uses. PB - Thanks! Donovan -- D Brooke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
What's your problem/reason with how it is? I assume Andy means: leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
On Jan 8, 2012, at 2:21 PM, Donovan Brooke wrote: Hello, I'm doing an insert into with date and time type fields. I was reading: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-literals.html My question is: is the format always 'year month day'?.. or can we save dates in 'month day year' as well? In DATE, DATETIME, and TIMESTAMP columns, you must specify the date part on year-month-day order. If you want to store a value in a different format, you must use some other data type such as VARCHAR. But then it won't be interpreted as a date. If you want to display a date from a DATE, etc. column in some other format, pass the value to DATE_FORMAT(). http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format If you want to reformat a date value in some other format to put it in year-month-day format so that you can store it in a DATE, etc. column, STR_TO_DATE() might be helpful. http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_str-to-date STR_TO_DATE() can be useful, for example, when loading non year-month-day data into a table with LOAD DATA. You can use STR_TO_DATE() to reformat the values on the fly. LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE t (name,@date,value) SET date = STR_TO_DATE(@date,'%m/%d/%y'); -- Paul DuBois Oracle Corporation / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Date and Time
leave it stored as a timestamp type or datetime type, and when you need to display it otherwise.. then covert with date() oops, Paul's post reminded me I was suggesting a PHP function here ^^^ ... and this is the MySQL list. -G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Default Date and Time
Hi All, I want to create a table that defaults to current_date and current_time. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Default Date and Time
Hi Jason The DEFAULT value can't be an expression. 2009/6/26 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi All, I want to create a table that defaults to current_date and current_time. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
RE: Default Date and Time
-Original Message- From: Jason Todd Slack-Moehrle [mailto:mailingli...@mailnewsrss.com] Sent: Friday, June 26, 2009 1:10 PM To: mysql@lists.mysql.com Subject: Default Date and Time Hi All, I want to create a table that defaults to current_date and current_time. [JS] Would a timestamp data type work for you? It includes both the date and time, and is updated whenever the record is changed. If not, you'll have to assign values in your INSERT statement using the time/date functions. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jschwa...@the- infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
find date an time of a table update
Hi, I didn't find a way to know the time and date of the last update of table, not the data but the table info itself. Is this possible? I want to use this info in order to show it in a web page. Mysql 4.1 Apache Myisam Regards Marcelo Fabiani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: find date an time of a table update
Hi, mysql show table status like 'tablename'\G will report you the date and time of creation, updation,etc of the specified table. 'SHOW STATUS' enables only view. Note 1: For some storage engines, this value is NULL. For example, InnoDB stores multiple tables in its tablespace and the data file timestamp does not apply. As you have specified that you want it to display it in the webpage, then retrieve the same from the 'tables' table of information_schema database. mysql use information_schema; mysql show tables; mysql select * from TABLES where TABLE_NAME='tablename'; Note 2: Check the user privilege to access the database. If not grant it. Thanks ViSolve DB Team. - Original Message - From: Marcelo Fabiani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, December 22, 2006 6:21 AM Subject: find date an time of a table update Hi, I didn't find a way to know the time and date of the last update of table, not the data but the table info itself. Is this possible? I want to use this info in order to show it in a web page. Mysql 4.1 Apache Myisam Regards Marcelo Fabiani -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to handle date and time conversions
I have read somewhere how to do the conversions but for some reason I can not find it again. Now I need to do conversions soon. Could someone direct me to where the documentation is located? Thanks for any help you can offer. Bob Rawlinson -- Robert A. Rawlinson Felicity Ohio 45120 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to handle date and time conversions
I'm not sure what you mean by date *conversions* but the date *functions* can be found here: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html. You will probably use these for most conversions you attempt. Rhino - Original Message - From: Robert A. Rawlinson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Sunday, May 08, 2005 8:17 AM Subject: How to handle date and time conversions I have read somewhere how to do the conversions but for some reason I can not find it again. Now I need to do conversions soon. Could someone direct me to where the documentation is located? Thanks for any help you can offer. Bob Rawlinson -- Robert A. Rawlinson Felicity Ohio 45120 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.11.6 - Release Date: 06/05/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date and time functions.
Hi All, I trying to add one year to date function is there a good documentation on how we can manipulate on date function. Changing the format of dates for mysql. Thank you , Harry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date and time functions.
Date modification functions are listed in the manual here: http://dev.mysql.com/doc/mysql/en/Date_and_time_functions.html If you are just trying to add a year to a column try something like this. update table set col = col + interval 1 year; -Eric On Sun, 4 Jul 2004 18:22:04 +1000, Hari Yellina [EMAIL PROTECTED] wrote: Hi All, I trying to add one year to date function is there a good documentation on how we can manipulate on date function. Changing the format of dates for mysql. Thank you , Harry -- 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: Migrating Access Tables -- Empty Columns, Date and Time
Bob My versions are Access 2000 and MySQL 4.0.20. Here's what I do. When exporting my Access table, I choose the file type Text Files. Then, on the next dialog box, make sure to click the Advanced button to get to the formatting information. Change the date order to YMD and the date delimiter to -. That will take care of the date. As for the NULL value. The table into which I'm importing has columns defined to allow NULLs, but when I import the Access table with empty columns, like you describe, it doesn't force a null -- it just leaves the value blank. It doesn't cause any problems, but if you want to force a NULL then I guess you could rework the exported file to state NULL for the empty fields you want to read as such. Wes On Jun 28, 2004, at 10:20 PM, Robert L Cochran wrote: 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating Access Tables -- Empty Columns, Date and Time
Why don't you use the export utility in ms access to load the data into mysql. The export will create the table in the database. All you need is a dsn connection and the mysql odbc. Osvaldo Sommer -Original Message- From: Robert L Cochran [mailto:[EMAIL PROTECTED] Sent: Monday, June 28, 2004 8:21 PM To: [EMAIL PROTECTED] Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: WEEKEND,,8,1,,0,,,at,,,2/12/1998 0:00:00,11/27/1998 0:00:00,,MB Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 6/27/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrating Access Tables -- Empty Columns, Date and Time
Show warnings is not available until 4.1.x ... The empty field will be imported as that. Empty and not a null value. ... Have you tried importing the data yet to see how the date fields look in MySQL? -Original Message- From: Robert L Cochran To: [EMAIL PROTECTED] Sent: 6/28/04 9:20 PM Subject: Migrating Access Tables -- Empty Columns, Date and Time I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: WEEKEND,,8,1,,0,,,at,,,2/12/1998 0:00:00,11/27/1998 0:00:00,,MB Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- 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]
Migrating Access Tables -- Empty Columns, Date and Time
I'm migrating a Microsoft Access 2002 (Service Pack 3) table constructed by my wife to a corresponding table in MySQL 4.0.20. Some columns in most of the 3000+ rows are empty. Some of these are contiguous empty columns. I don't know if Access considers them NULL or not, but when you export an Access row containing empty columns to a comma separated values file, the empty column will be represented by a sequence of placeholder commas. Here is a part of the first table row exported by Access: WEEKEND,,8,1,,0,,,at,,,2/12/1998 0:00:00,11/27/1998 0:00:00,,MB Based on recent experience with loading a simpler Access table, these empty columns will be imported as is by both mysqlimport and LOAD DATA LOCAL INFILE, but with warnings. I have 2 questions associated with this: 1) How do I make mysqlimport or LOAD DATA LOCAL tell me the text of each warning? By default they print a summary count of warnings but don't issue actual warning messages. The default log files show nothing. mysqlimport -v does not do it. 2) When consecutive commas (meaning at least 1 empty column, sometimes several) are seen, what does mysqlimport/LOAD DATA do to the corresponding column entrie(s)? Will it set them to NULL? Or to the default specified in the CREATE TABLE statement? Should I explicitly set these to NULL where permitted by the column type? Last of all, look at this date and time stamp exported by Access: ,2/12/1998 0:00:00, Will mysqlimport choke on this, since MySQL likes dates to be in ccyy-mm-dd format? Will I need to reformat the date with a sed script? Thanks Bob Cochran Greenbelt, Maryland, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i make mysql to print date and time automatically?
INSERT INTO your_table (field1, field2, field3) VALUES(DATE(), value2, TIME()) But check the manual for very simple things. Thanks Emery - Original Message - From: Emilio Ruben Estevez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 17:09 Subject: How can i make mysql to print date and time automatically? Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Thaks in advance. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- 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: How can i make mysql to print date and time automatically?
I am sorry, the instruction I sent doesn't work in MySQL: You should use: INSERT INTO your_table (field1, field2, field3) VALUES(CURDATE(), value2, CURTIME()) Note the CUR (I think it stands for CURrent) Thanks Emery - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Emilio Ruben Estevez [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 22:16 Subject: Re: How can i make mysql to print date and time automatically? INSERT INTO your_table (field1, field2, field3) VALUES(DATE(), value2, TIME()) But check the manual for very simple things. Thanks Emery - Original Message - From: Emilio Ruben Estevez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 17:09 Subject: How can i make mysql to print date and time automatically? Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Thaks in advance. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can i make mysql to print date and time automatically?
Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Thaks in advance. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can i make mysql to print date and time automatically?
On Wed, 2003-10-01 at 10:09, Emilio Ruben Estevez wrote: Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Heres a hint...type date into the search field on www.mysql.com instead of asking the good people here to hold your hand. Yes I am a little irritated at people who don't even make the simplest of efforts to help themselves. Stepping down...and apologizing for the attitude to the good and helpful people here. -- Woody In a world without boundaries why do we need Gates and Windows? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
date and time
Hi, can someone explain me the avantage of using date and time, and also can i set time + XX minutes?? Thanx Anthony
Re: date and time
Hi, can someone explain me the avantage of using date and time, and also can i set time + XX minutes?? Thanx Anthony Short answer is: You can use the date and time functions and formats. There is one or just use '+' or '-' See chapter 6 in the ref. manual. If you need both date and time use the datetime field type since you will simplify sorting and conditionals. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Field + Time Field = Datetime Field?
Alex, Further to Anvar's suggestions: - in addition to time_to_sec() recommend you look at sec_to_time() - which saves the repeated division to produce 'elapsed time' - alternatively take a look at unix_timestamp() and from_unixtime() which would allow the recording of all times to the second/as seconds, and convert to time/date presentation formats as required - regardless, unless you want large values expressed as hours (even beyond 24 hours) all elapsed time calculations must be divided by 86400 so that the integral part can be passed into a 'date' function, and the remainder into a 'time' function Times/dates can be stored ready for calculation or ready for display - it is always easier to convert a calculated field for display, than to perform arithmetic on a formatted-for-display field! Regards, =dn - Original Message - From: Anvar Hussain K.M. [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 11 January 2002 04:05 Subject: Re: Date Field + Time Field = Datetime Field? Hi Alex, I don't think your problem will solved by making the time columns to full datetime columns as there is no functions to subtract two datetime values directly. But you can can keep the time columns and go on like this: convert the time into seconds using time_to_sec function. subtract fromtime from totime. this will yield the difference in seconds now you can take the elapsed time in hours,minutes or seconds. The sql might be something like: Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours from table. Section 7.4.11 of the manual explains date and time functions. Anvar At 06:00 PM 10/01/2002 +, you wrote: I've been working with a timesheet database, where all the employees of my small business enter in the hours they work on projects. I've been storing, for each record, a date of work, a start time, and a finish time. When I attempted to write a Perl script to display invoices, though, I ran into the issue that subtracting one time from another yields inconsistent results. Thus, I'm going to convert to datetime fields, which would store just the start and finish times as datetime. I'd like to automate the switchover, so I don't have to go through and maually update. I thought at first that UPDATE time_worked SET dtstart = concat(datework, start); would work, but that gave me a syntax error. I can't seem to find anything in the manual that would help, either. Is there any way to do this without going through and manually updating? I'd really appreciate any hints you could give. FYI, I'm running MySQL 3.23.37. Thanks, Alex Kirk - 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 - 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 Field + Time Field = Datetime Field?
Hi Alex, I don't think your problem will solved by making the time columns to full datetime columns as there is no functions to subtract two datetime values directly. But you can can keep the time columns and go on like this: convert the time into seconds using time_to_sec function. subtract fromtime from totime. this will yield the difference in seconds now you can take the elapsed time in hours,minutes or seconds. The sql might be something like: Select (time_to_sec(totime) - time_to_sec(fromtime)) / 60 / 60 as hours from table. Section 7.4.11 of the manual explains date and time functions. Anvar At 06:00 PM 10/01/2002 +, you wrote: I've been working with a timesheet database, where all the employees of my small business enter in the hours they work on projects. I've been storing, for each record, a date of work, a start time, and a finish time. When I attempted to write a Perl script to display invoices, though, I ran into the issue that subtracting one time from another yields inconsistent results. Thus, I'm going to convert to datetime fields, which would store just the start and finish times as datetime. I'd like to automate the switchover, so I don't have to go through and maually update. I thought at first that UPDATE time_worked SET dtstart = concat(datework, start); would work, but that gave me a syntax error. I can't seem to find anything in the manual that would help, either. Is there any way to do this without going through and manually updating? I'd really appreciate any hints you could give. FYI, I'm running MySQL 3.23.37. Thanks, Alex Kirk - 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