Page 1 of 1

Add 1 day to timestamp

Posted: Sat Aug 01, 2009 2:51 am
by raggy99
I have a search with in my website which searches the following Company, Type, Date and date1 fields
so you can search a company with the type between 2 dates i.e. 2009-01-01 - 2009-07-05
now my search works however because i'm using a sql timeanddate stamp when the when you search between to dates it only searches from 2009-01-01 00:00:01 to 2009-07-05 00:00:01
so no records show on that date.

i have found my mysql's site that you can add 1 day to it. but i'm not sure how to implement this as I am using dreamweaver. :banghead:

Let me know and i will post code if you so wish.

Please help.

Re: Add 1 day to timestamp

Posted: Sat Aug 01, 2009 5:33 am
by aceconcepts
Take a look at mktime - http://uk3.php.net/mktime

Re: Add 1 day to timestamp

Posted: Sat Aug 01, 2009 9:30 am
by jayshields
In MySQL you could just append "23:59:59" onto the maximum date. So this:

Code: Select all

SELECT
  *
FROM
  `tbl`
WHERE
  `date`
BETWEEN
  '2009-01-01'
AND
  '2009-07-05'
Would become:

Code: Select all

SELECT
  *
FROM
  `tbl`
WHERE
  `date`
BETWEEN
  '2009-01-01'
AND
  '2009-07-05 23:59:59'

Re: Add 1 day to timestamp

Posted: Sun Aug 02, 2009 2:57 am
by raggy99
jayshields wrote: Would become:

Code: Select all

SELECT
  *
FROM
  `tbl`
WHERE
  `date`
BETWEEN
  '2009-01-01'
AND
  '2009-07-05 23:59:59'
Remember i'm doing this in dreamweaver

Code: Select all

 
SELECT *
FROM log
WHERE CompanyName LIKE '%colname%' AND type LIKE '%colname2%' AND dateandtime >= 'colname3' AND dateandtime <= 'colname4 23:59:59'
 
Colname = Company Name
Colname2 = type
colname3 = date
colname4 = date1

i have tried to implement it as above. but i'm getting errors. how do i implement this?

Re: Add 1 day to timestamp

Posted: Sun Aug 02, 2009 10:24 am
by jayshields
Doing it in Dreamweaver should make no difference. What errors are you getting?

Re: Add 1 day to timestamp

Posted: Wed Aug 05, 2009 2:29 pm
by aceconcepts
Your colnames should appear as variables correct?

If so, use the dollar sign e.g.

Code: Select all

'%$colname%'