Re: Timestamp woes

[Michael Stassen]

Scott Plumlee wrote:

[EMAIL PROTECTED] wrote:

Hi,

(Please note: NEWBIE WARNING, below questions might sound stupid, but 
feel free to flame.) ;-)

I have a table "tbl_users" with a field "dat_an_time" which is a
timestamp(14).
In that I have values such as:
2004022215
20040227042018
20040223015329
etc
I have searched google/the manual for the answers to the below 
questions but I only found answers if the field is a datetime field
and not a timestamp, I cannot change the format as I am the new 
developer of an old (already live) project and lots of scripts are 
already accessing this table.

Please tell me how to do this with my current setup.

Two questions:
Question 1.
In my php script I am taking the value of "days_last" which should
run a select query to display all the records in the last
$days_last days (for those of you who don't program in PHP/Perl
$days_last is the variable that is a number which the user selects.
eg: 10 or 5 or 35 etc)
How do I format the query to get the results from my "dat_an_time"
timestamp(14) field?
  SELECT * FROM tbl_users
  WHERE dat_an_time > CUR_DATE() - INTERVAL $days_last DAY
Depending on exactly what you want, you may need to change the ">" to 
">=".  That is, if $last_days is 1, do you want today only (>), or 
yesterday and today (>=).

Question 2.
The client will be entering 2 fields in this format MMDD, how
do I "select * " for all records that are between $T_field1 and
$T_field2 from my "dat_an_time" timestamp(14) my records are like
this:
2004022215
20040227042018
20040223015329
  SELECT * FROM tbl_users
  WHERE data_an_time BETWEEN $T_field1 AND $T_field2
Any help, references to the manual or URLs will be appreciated.
See  and 
 for details.

Thanks in advance.
Ryan.
Somewhat new myself but I believe you can do something like SELECT 
(whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - 
($days_last*24*60*60)) < UNIX_TIMESTAMP(dat_and_tim).
Note that UNIX_TIMESTAMP defaults to NOW(), so you can simply write 
UNIX_TIMESTAMP() instead of UNIX_TIMESTAMP(NOW()).

I see 2 problems with this approach:

1. This retrieves records starting at the current time on the day which 
is $days_last days ago.  That is, if you run this query at noon, you 
won't get records from the morning of the first day in the range. 
That's fine if that's what you want, but I'm not sure that's what Ryan 
had in mind.

2. By comparing the result of a function of the dat_an_time column, you 
prevent the use of any index on that column.  Depending on the size of 
the table and the context of the query, that could yield unacceptably 
slow results.  If at all possible, you should compare the value of a 
column to a constant rather than a function of a column to a function of 
a constant.  With that in mind, if you want time of day to matter, you 
could rewrite your query as

  SELECT * FROM tbl_users
  WHERE
  dat_an_time > FROM_UNIXTIME(UNIX_TIMESTAMP() - $days_last*24*60*60)
or, better yet,

  SELECT * FROM tbl_users
  WHERE
  dat_an_time > NOW() - INTERVAL $days_last DAY
Michael



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Timestamp woes

[Scott Plumlee]

Ryan A wrote:

/**
Somewhat new myself but I believe you can do something like SELECT
(whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) -
($days_last*24*60*60)) < UNIX_TIMESTAMP(dat_and_tim).
I will add the the PHP Cookbook and the MySQL Cookbook are godsends and
that's where I got the solution.  Grab a copy of these to help out -
I've got a project due in a week and I'm using these non-stop.
***/
Hey,
Thanks its working like a charm now to get the number of daysbut am
still confused on
how do I do the second comparision: selecting been sayyy 15th of Feb and
23rd of Feb..
any ideas?
Thanks,
-Ryan
Again, I think you can convert each of the bookend dates to a 
unix-format time (time since epoch) in PHP (I assume that those dates 
aren't entered in the db) and then use that same UNIX_TIMESTAMP function 
on your table entries.  Then pull entries out where the table values are 
 between those two values.

--

Scott Plumlee
PGP Public key: http://plumlee.org/pgp/   D64C 47D9 B855 5829 D22A  D390 
F8E2 9B58 9CBF 1F8D

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Timestamp woes

[Scott Plumlee]

[EMAIL PROTECTED] wrote:

Hi,

(Please note: NEWBIE WARNING, below questions might sound stupid, but feel
free to flame.) ;-)
I have a table "tbl_users" with a field "dat_an_time" which is a
timestamp(14).
In that I have values such as:
2004022215
20040227042018
20040223015329
etc
I have searched google/the manual for the answers to the below questions but
I only found answers if
the field is a datetime field and not a timestamp, I cannot change the
format as I am the new
developer of an old (already live) project and lots of scripts are already
accessing this table.
Please tell me how to do this with my current setup.

Two questions:
Question 1.
In my php script I am taking the value of "days_last" which should run a
select query to display all the
records in the last $days_last days (for those of you who dont program in
PHP/Perl $days_last is the
variable that is a number which the user selects. eg: 10 or 5 or 35 etc)
How do I format the query to get the results from my "dat_an_time"
timestamp(14) field?
Somewhat new myself but I believe you can do something like SELECT 
(whatever you need) FROM tbl_users WHERE (UNIX_TIMESTAMP(now()) - 
($days_last*24*60*60)) < UNIX_TIMESTAMP(dat_and_tim).

I will add the the PHP Cookbook and the MySQL Cookbook are godsends and 
that's where I got the solution.  Grab a copy of these to help out - 
I've got a project due in a week and I'm using these non-stop.

--

Scott Plumlee
PGP Public key: http://plumlee.org/pgp/   D64C 47D9 B855 5829 D22A  D390 
F8E2 9B58 9CBF 1F8D

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Timestamp woes

[ryan]

Hi,

(Please note: NEWBIE WARNING, below questions might sound stupid, but feel
free to flame.) ;-)

I have a table "tbl_users" with a field "dat_an_time" which is a
timestamp(14).
In that I have values such as:
2004022215
20040227042018
20040223015329
etc

I have searched google/the manual for the answers to the below questions but
I only found answers if
the field is a datetime field and not a timestamp, I cannot change the
format as I am the new
developer of an old (already live) project and lots of scripts are already
accessing this table.

Please tell me how to do this with my current setup.

Two questions:
Question 1.
In my php script I am taking the value of "days_last" which should run a
select query to display all the
records in the last $days_last days (for those of you who dont program in
PHP/Perl $days_last is the
variable that is a number which the user selects. eg: 10 or 5 or 35 etc)

How do I format the query to get the results from my "dat_an_time"
timestamp(14) field?

Question 2.
The client will be entering 2 fields in this format MMDD, how do I
"select * " for all records that
are between $T_field1 and $T_field2 from my "dat_an_time" timestamp(14)
my records are like this:
2004022215
20040227042018
20040223015329

Any help, references to the manual or URLs will be appreciated.

Thanks in advance.
Ryan.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]