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:
20040222000015
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 YYYYMMDD, 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:
20040222000015
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 <http://www.mysql.com/doc/en/DATETIME.html> and 
<http://www.mysql.com/doc/en/Date_and_time_functions.html> 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]