Re: DATE problem
Hello. MySQL doesn't support this syntax. See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html If you want to automatically extract the year (month, day) part from the inserted value, you may want to use TRIGGERS, however, in my opinion, it is better to redesign your table structure. You can use VIEWS to make the design of your database more flexible. See: http://dev.mysql.com/doc/refman/5.0/en/views.html http://dev.mysql.com/doc/refman/5.0/en/triggers.html Mester József wrote: > Hy all > > I would like to get date in my web page. > There are three different fields. YEAR MONTH and DAY > I thought in SQL possible create table like this /*from my book*/ > > create table dates ( input_year DATETIME YEAR TO YEAR, > input_month DATETIME MONTH TO MONTH, > input_day DATETIME DAY TO DAY); > > In this example I can store year,month and day values in different fields. > Mysql doesn't accept this create statement. > > Joe > > > > > - > Yahoo! Messenger NEW - crystal clear PC to PC calling worldwide with > voicemail -- 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]
Re: DATE problem
Mike Johnson wrote: From: René Fournier [mailto:[EMAIL PROTECTED] I'm trying to count rows that were added today. The column that I am counting on is in DATETIME format, so there are hours and minutes and seconds recorded, but I only need to check the date $sql = "SELECT COUNT(table.id) FROM table WHERE table.created = NOW()"; $num = mysql_result(mysql_query($sql),0); The problem with this is that unless the record was added at precisely the same time as NOW()-which never happens-no rows are returned. Is there a way I can round off table.created to just a DATE, then compare it to CURDATE()?? I've been reading DATE Format functions, but am not sure how to proceed. Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! It will work, but you don't want to do that. As soon as you run your column through a function, you can no longer use the index on that column to choose rows. In other words, this query results in a full table scan. Mysql will have to execute DATE_FORMAT() on table.created for every single row to make the comparison. Instead, you should always compare columns to constants, if possible. That's not as bad as it may sound, since functions of constants are constants. In this case (assuming no rows with created in the future), you should use SELECT COUNT(table.id) FROM table WHERE table.created >= CURDATE(); Mysql will calculate CURDATE() once, convert it to a DATETIME by adding zeros, then select matching rows using the index on table.created. Another example: Say you wanted the rows which were created in the last 72 hours. Here are some equivalent conditions: WHERE UNIX_TIMESTAMP() - UNIX_TIMESTAMP(created) <= 72*60*60 WHERE created + INTERVAL 72 HOUR >= NOW() WHERE created >= NOW() - INTERVAL 72 HOUR Only the last one can use an index on created, however. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATE problem
From: René Fournier [mailto:[EMAIL PROTECTED] > I'm trying to count rows that were added today. The column that I am > counting on is in DATETIME format, so there are hours and minutes and > seconds recorded, but I only need to check the date > > $sql ="SELECT > COUNT(table.id) > FROM table > WHERE table.created = NOW()"; > > $num = mysql_result(mysql_query($sql),0); > > The problem with this is that unless the record was added at > precisely the same time as NOW()-which never happens-no rows are > returned. Is there a way I can round off table.created to just a > DATE, then compare it to CURDATE()?? I've been reading DATE > Format functions, but am not sure how to proceed. > > Thanks in advance. Try this: SELECT COUNT(table.id) FROM table WHERE DATE_FORMAT(table.created, '%Y-%m-%d') = CURDATE(); Should do what you're looking for. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
Richard Davey wrote: Hello Eric, Tuesday, March 9, 2004, 11:46:23 PM, you wrote: ES> We have a table with a Date Time field and we need to update only the date ES> portion of that field. ES> We have table a with field Foo with value '2004-01-01 12:15:00' and a date ES> '2004-03-01' and we need to change the date portion of Foo to the date and ES> leave the time part alone. ES> So Foo would change from: ES> '2004-01-01 12:15:00' ES> to: ES> '2004-03-01 12:15:00' I would have thought that: UPDATE x SET y = DATE_ADD(y, INTERVAL 2 DAY) But that is adding two days, the original query was to add two months, so presumably it should be "INTERVAL 2 MONTH" - but beware that MySQL does some seriously bizarre things with dates - for example adding 2 months to 31st Dec takes you to 31st Feb which probably isn't what you want. would work and not alter the time, but only a test will tell. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
Hello Eric, Tuesday, March 9, 2004, 11:46:23 PM, you wrote: ES> We have a table with a Date Time field and we need to update only the date ES> portion of that field. ES> We have table a with field Foo with value '2004-01-01 12:15:00' and a date ES> '2004-03-01' and we need to change the date portion of Foo to the date and ES> leave the time part alone. ES> So Foo would change from: ES> '2004-01-01 12:15:00' ES> to: ES> '2004-03-01 12:15:00' I would have thought that: UPDATE x SET y = DATE_ADD(y, INTERVAL 2 DAY) would work and not alter the time, but only a test will tell. -- Best regards, Richard Davey http://www.phpcommunity.org/wiki/296.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date Problem
* Girish Agarwal > Hi All, >I have added the date column ( column name is > dolv ) in mysql as CHAR(10) Then it is not a 'date column', but s string column... ;) See the manual for a description of the different column types: http://www.mysql.com/doc/en/Column_types.html > http://www.mysql.com/doc/en/Date_and_time_types.html > http://www.mysql.com/doc/en/DATETIME.html > There are many special functions for use with date & time type columns: http://www.mysql.com/doc/en/Date_and_time_functions.html > > as I needed the date in > mmdd separated by mm/dd/. The formatting of the date column should be done when you query the table: SELECT *,DATE_FORMAT(dolv,"%m/%d/%Y") AS dolv FROM table WHERE ... > Now I want to have > this column contain the date whenever the updation to > that column is done. Then you could use the TIMESTAMP coulmn type, which does exactly this, if you meant "... whenever the updation to that ROW is done.". If you use a DATETIME column type, you could use the NOW() function: UPDATE table SET some_column = 'new value', other_column = 'other value', dolv = NOW(); The TIMESTAMP column does this automatically. > Please advise as to how I can > achieve this without losing the data I have already > entered. To convert your existing rows to proper dates: # make a backup of the entire table CREATE TABLE backup_table SELECT * FROM table # create a new column: ALTER TABLE table ADD dolvdate TIMESTAMP; (Use DATETIME or DATE instead, if you don't wan't the TIMESTAMP "magic".) Now we populate the new dolvdate column with a date created by substrings from the dolv column. We must split the "mmddyyy" or "mm/dd/" strings into "mm", "dd", and "", so that we can feed them to mysql as -mm-dd. # Fix dates in "mm/dd/" format: UPDATE table SET dolvdate = CONCAT( MID(dolv,7,4),'-',MID(dolv,1,2),'-',MID(dolv,4,2)); # Fix remaining dates in "mmdd" format: UPDATE table SET dolvdate = CONCAT( MID(dolv,5,4),'-',MID(dolv,1,2),'-',mid(MID,3,2)) WHERE dolvdate = '0'; # check that everything is converted: SELECT COUNT(*) FROM table WHERE dolvdate = '0'; # if something is not converted, check what it is: SELECT * FROM table WHERE dolvdate = '0' LIMIT 10; # remove old column ALTER TABLE table DROP dolv; # rename new column ALTER TABLE table CHANGE dolvdate dolv TIMESTAMP; HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: date problem
On 24-Mar-2003 Michael Shulman wrote:
> It looks like it's in now() format, but without the punctuation. The
> line
> from your insert statement is
Nope, that's a MySQL timestamp.
UNIX_TIMESTAMPS are # of seconds from the epoch (Jan 1 1970).
"nobody" <[EMAIL PROTECTED]> is trying to store an INT in a timestamp column and
probably clobbering his values.
>
> 20030323225645
>
> If we break this apart, we see:
>
> Year = 2003
> Month = 03
> Date = 23
> Hour = 22 (or 10pm)
> Minute 56
> Seconds 45
>
Nope, that's a MySQL timestamp.
UNIX_TIMESTAMPS are # of seconds since the epoch (Jan 1 1970).
nobody <[EMAIL PROTECTED]> is probably trying to store an INT in a timestamp
column and clobbering his values as the timestamp updates.
> Someone else on the list: where or how is the timezone encode, or this is
> entry in GMT? Is there a way to determine the local timezone on the
> machine?
>
mysql> show variables like 'timezone';
+---+---+
| Variable_name | Value |
+---+---+
| timezone | PST |
+---+---+
1 row in set (0.00 sec)
mysql> select sec_to_time((time_to_sec(now()) - UNIX_TIMESTAMP()) % 86400)
as 'offset GMT';
++
| offset GMT |
++
| -08:00:00 |
++
1 row in set (0.00 sec)
>
> -Original Message-
> From: nobody [mailto:[EMAIL PROTECTED]
> Sent: Sunday, March 23, 2003 12:56 PM
> To: [EMAIL PROTECTED]
> Subject: date problem
>
> Hello mysql,
>
> I do this:
>
> $query = "INSERT INTO news(ID, author, title, text, date) VALUES(NULL,
> '".$_SESSION["ulogged"]."', '".$title."', '".$text."',
> UNIX_TIMESTAMP(NOW()))";
> $result = mysql_query($query, $connection) or die("problem with query");
>
> I get this:
>
> mysql> select * from news where id='1';
> +++--+++
>| ID | author | title| text | date |
> +++--+++
>| 1 | myuser | news | blah blah blah | 20030323225645 |
> +++--+++
> 1 row in set (0.01 sec)
>
> Look at the time! It's set ... strange :) 2003 03 23 22 56 45 .. it's
> not neither now() format, neither unix_timestamp() format.
>
> mysql> select now(), unix_timestamp(now());
> +-+---+
>| now() | unix_timestamp(now()) |
> +-+---+
>| 2003-03-23 23:07:30 |1048453650 |
> +-+---+
> 1 row in set (0.01 sec)
>
> It's okay. So, why in the query from a php form the unix time date is
> saved wrong? Any ideas and suggestions?
>
> --
> best wishes,
> Strahil Minev a.k.a. DLHelper,
> BuFu TeaM mailto:[EMAIL PROTECTED]
>
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
(53kr33t w0rdz: sql table query)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: date problem
It looks like it's in now() format, but without the punctuation. The line
from your insert statement is
20030323225645
If we break this apart, we see:
Year = 2003
Month = 03
Date = 23
Hour = 22 (or 10pm)
Minute 56
Seconds 45
Someone else on the list: where or how is the timezone encode, or this is
entry in GMT? Is there a way to determine the local timezone on the machine?
-ms
-Original Message-
From: nobody [mailto:[EMAIL PROTECTED]
Sent: Sunday, March 23, 2003 12:56 PM
To: [EMAIL PROTECTED]
Subject: date problem
Hello mysql,
I do this:
$query = "INSERT INTO news(ID, author, title, text, date) VALUES(NULL,
'".$_SESSION["ulogged"]."', '".$title."', '".$text."',
UNIX_TIMESTAMP(NOW()))";
$result = mysql_query($query, $connection) or die("problem with query");
I get this:
mysql> select * from news where id='1';
+++--+++
| ID | author | title| text | date |
+++--+++
| 1 | myuser | news | blah blah blah | 20030323225645 |
+++--+++
1 row in set (0.01 sec)
Look at the time! It's set ... strange :) 2003 03 23 22 56 45 .. it's
not neither now() format, neither unix_timestamp() format.
mysql> select now(), unix_timestamp(now());
+-+---+
| now() | unix_timestamp(now()) |
+-+---+
| 2003-03-23 23:07:30 |1048453650 |
+-+---+
1 row in set (0.01 sec)
It's okay. So, why in the query from a php form the unix time date is
saved wrong? Any ideas and suggestions?
--
best wishes,
Strahil Minev a.k.a. DLHelper,
BuFu TeaM mailto:[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]
Re: date problem
-do you have any index covering this query? -this is not a good practice to have function call(s) in the where clause, since mysql will make the fucntion call for every rows scanned. select count(*) as c from users_table where user_regdate > "2002-06-01 00:00:00" -furthermore, in the where clause, >= is always better than >. select count(*) as c from users_table where user_regdate >= "2002-06-01 00:00:01" andy wrote: > > Hi there, > > I would like to count the users out of a mysql db who registered after a > certain date. > > The column I have in the db is a char and I do not want to change this > anymore. > This is how a typical entry looks like: May 29, 2002 > > This is how I tryed it: > > // while '10...' is unix timestamp june 1, 02 > SELECT COUNT(*) AS c > FROM users_table > WHERE UNIX_TIMESTAMP( user_regdate ) > '1022882400' > > Thanx for any help on that, > > andy > > 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 -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without it. - 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: Re: Date Problem
Paul, Friday, May 17, 2002, 12:11:37 PM, you wrote: >> I have a column in my MySql database with datetime column.i want to get P> only date >> part of that column .is any easy way to do that. Look at DATE_FORMAT() function: http://www.mysql.com/doc/D/a/Date_and_time_functions.html >> Thanks a Lot >> Paul -- 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
Re: Date Problem
> > Hi All > I have a column in my MySql database with datetime column.i want to get only date > part of that column .is any easy way to do that. > Thanks a Lot > Paul > > > - 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 problem
>I got a problem in converting the Jan 21 2001 format to 2001-01-21
>I always got a null as result when i do date_format('Jan 21
>2001','%Y-%m-%d')
>
>can anyone help me with this
Sir, off the top of my head, I don't think there's a function for
this. Date_format() converts from MySQL format to other formats, but
not the other way around.
The following will reorder the parts of the date, but for converting
'Jan' to '1', you're on your own. I would probably do it with a
lookup table, but someone else may know a better way.
Concat(Substring_index(my_date, " ", -1), "-",
Substring_index(my_date, " ", 1), "-",
Substring_index(Substring_index(my_date, " ", -2), " ", 1))
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
-
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
