DATETIME
|
View:
New views
5 Messages
—
Rating Filter:
Alert me
|
 |
DATETIME
by Edward Brookhouse
::
Rate this Message:
Hi All,
I have a DB that is storing the date/time an event happens in DATETIME format, i.e. 1254252889, which translates to Tue, 29 Sep 2009 19:34:49 UTC I am trying to write a query in PHP that will look for any row that falls within a range of dates, i.e. between Sep 1 and Oct 1, but there doesn't seem to be a way to search, since you would never be able to specify a match between what date range you put in, and the time stamps?? I created a form that allows them to select a date, but even converting a date string to a timestamp, you'll never be able to get a match in the db...? Any thoughts appreciated, Edward |
|
|
Re: DATETIME
by Dan Shirah
::
Rate this Message:
>
> Hi All, > > > > I have a DB that is storing the date/time an event happens in DATETIME > format, i.e. 1254252889, which translates to Tue, 29 Sep 2009 19:34:49 UTC > > > > I am trying to write a query in PHP that will look for any row that falls > within a range of dates, i.e. between Sep 1 and Oct 1, but there doesn't > seem to be a way to search, since you would never be able to specify a match > between what date range you put in, and the time stamps?? > > > > I created a form that allows them to select a date, but even converting a > date string to a timestamp, you'll never be able to get a match in the > db...? > > > > Any thoughts appreciated, > > > > Edward I'm not sure I'm following you. 1) What database are you using? MSSQL/Informix/MySQL? 2) The data type for your event date/time column is just DATETIME? Or is it DATETIME YEAR TO FRACTION or anything like that? 3) Wouldn't your query be written in SQL and just executed from PHP? If you are using DATETIME YEAR TO FRACTION your query should look something like: "SELECT * FROM my_table WHERE event_date BETWEEN ('2009-09-01 00:00:00.000' AND '2009-10-01 23:59:59.999')" |
|
|
Re: DATETIME
by Jason Gerfen-2
::
Rate this Message:
Go look at dev.mysql.com and search for DATETIME. There are plenty of
examples of how to do comparison searching using the UNIX datestamps. Dan Shirah wrote: >> Hi All, >> >> >> >> I have a DB that is storing the date/time an event happens in DATETIME >> format, i.e. 1254252889, which translates to Tue, 29 Sep 2009 19:34:49 UTC >> >> >> >> I am trying to write a query in PHP that will look for any row that falls >> within a range of dates, i.e. between Sep 1 and Oct 1, but there doesn't >> seem to be a way to search, since you would never be able to specify a match >> between what date range you put in, and the time stamps?? >> >> >> >> I created a form that allows them to select a date, but even converting a >> date string to a timestamp, you'll never be able to get a match in the >> db...? >> >> >> >> Any thoughts appreciated, >> >> >> >> Edward >> > > > I'm not sure I'm following you. > > 1) What database are you using? MSSQL/Informix/MySQL? > > 2) The data type for your event date/time column is just DATETIME? Or is it > DATETIME YEAR TO FRACTION or anything like that? > > 3) Wouldn't your query be written in SQL and just executed from PHP? > > If you are using DATETIME YEAR TO FRACTION your query should look something > like: > > "SELECT * FROM my_table WHERE event_date BETWEEN ('2009-09-01 00:00:00.000' > AND '2009-10-01 23:59:59.999')" > > -- Jason Gerfen Systems Administration/Web application development jason.gerfen@... Marriott Library Lab Systems PC 295 South 1500 East Salt Lake City, Utah 84112-0806 Ext 5-9810 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php |
|
|
Re: DATETIME
by Jason Gerfen-2
::
Rate this Message:
Sorry I should have included a simple example:
SELECT (`field_01`, `field_02`) FROM `table` WHERE DATETIME('2009-09-01 00:00:00.000') < DATETIME('2009-10-01 23:59:59.999'); Dan Shirah wrote: >> Hi All, >> >> >> >> I have a DB that is storing the date/time an event happens in DATETIME >> format, i.e. 1254252889, which translates to Tue, 29 Sep 2009 19:34:49 UTC >> >> >> >> I am trying to write a query in PHP that will look for any row that falls >> within a range of dates, i.e. between Sep 1 and Oct 1, but there doesn't >> seem to be a way to search, since you would never be able to specify a match >> between what date range you put in, and the time stamps?? >> >> >> >> I created a form that allows them to select a date, but even converting a >> date string to a timestamp, you'll never be able to get a match in the >> db...? >> >> >> >> Any thoughts appreciated, >> >> >> >> Edward >> > > > I'm not sure I'm following you. > > 1) What database are you using? MSSQL/Informix/MySQL? > > 2) The data type for your event date/time column is just DATETIME? Or is it > DATETIME YEAR TO FRACTION or anything like that? > > 3) Wouldn't your query be written in SQL and just executed from PHP? > > If you are using DATETIME YEAR TO FRACTION your query should look something > like: > > "SELECT * FROM my_table WHERE event_date BETWEEN ('2009-09-01 00:00:00.000' > AND '2009-10-01 23:59:59.999')" > > -- Jason Gerfen Systems Administration/Web application development jason.gerfen@... Marriott Library Lab Systems PC 295 South 1500 East Salt Lake City, Utah 84112-0806 Ext 5-9810 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php |
|
|
RE: DATETIME
by Edward Brookhouse
::
Rate this Message:
Thanks all - yes this is mysql, but not a mysql DATETIME field per se, but the issue was that I was trying to do like or equal to query the date range, and doing that will never match since whats in the db is YYYYMMDDHHMMSSSS (in timestamp format) and yet the query I would be able to send would never match anything exactly, so as Jason suggested, using 'BETWEEN' was the key But as a BETWEEN it's not a matter of matching, it's a matter of falling in a range.
I.e. Your timestamp is 150 Sep 01 is 100 Oct 01 is 200 150 passes the between 100 and 200 check. So the granularity should not be an issue. Thanks all Edward -----Original Message----- From: Jason Gerfen [mailto:jason.gerfen@...] Sent: Thursday, October 08, 2009 4:09 PM Cc: php-db@... Subject: Re: [PHP-DB] DATETIME Go look at dev.mysql.com and search for DATETIME. There are plenty of examples of how to do comparison searching using the UNIX datestamps. Dan Shirah wrote: >> Hi All, >> >> >> >> I have a DB that is storing the date/time an event happens in DATETIME >> format, i.e. 1254252889, which translates to Tue, 29 Sep 2009 19:34:49 UTC >> >> >> >> I am trying to write a query in PHP that will look for any row that falls >> within a range of dates, i.e. between Sep 1 and Oct 1, but there doesn't >> seem to be a way to search, since you would never be able to specify a match >> between what date range you put in, and the time stamps?? >> >> >> >> I created a form that allows them to select a date, but even converting a >> date string to a timestamp, you'll never be able to get a match in the >> db...? >> >> >> >> Any thoughts appreciated, >> >> >> >> Edward >> > > > I'm not sure I'm following you. > > 1) What database are you using? MSSQL/Informix/MySQL? > > 2) The data type for your event date/time column is just DATETIME? Or is it > DATETIME YEAR TO FRACTION or anything like that? > > 3) Wouldn't your query be written in SQL and just executed from PHP? > > If you are using DATETIME YEAR TO FRACTION your query should look something > like: > > "SELECT * FROM my_table WHERE event_date BETWEEN ('2009-09-01 00:00:00.000' > AND '2009-10-01 23:59:59.999')" > > -- Jason Gerfen Systems Administration/Web application development jason.gerfen@... Marriott Library Lab Systems PC 295 South 1500 East Salt Lake City, Utah 84112-0806 Ext 5-9810 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php |
| Free embeddable forum powered by Nabble | Forum Help |
