questions about time() and creating a date with a time of 12:00am of that day

[Jax2]

Hi guys...

 

I need to know how to do this, and quickly :/ I found a website where you could type in a date and it would give you the unix timestamp of that date at 12:00 am...

 

For example, if you type in todays date (04 09 2010) you'll get 1270771200

 

I am inserting records into a database and I need to be able to find all records that have a callback date of a certain time. The problem is, as it is now, it is inserting the time of day as well, so the string could look like this: 1270837382 which would be 6:23 pm on todays date.

 

I need to figure out how I would write it so that when it inserts the date, it is only inserting it as 12:00am on the day specified... (I use +86400 to add 1 day, +172800 for +2 days from now ...etc)

 

Let me make it clear:

 

I enter a new contact. I need to contact that person at a later date. I need the contact later date to be 12:00 am exactly 3 days from now. So I would take todays date (at 12:00am) and add 3 days, or 259200 to that timestamp ...

 

This way, when I need to find all records for clients that need to be contacted on a certain day, I can simply look up that day at 12:00am exactly and find the records with no problem.

 

If you are confused, PLEASE ask questions, I will explain better if I can ...

[ddubs]

You can use mktime to create a unix timestamp:

 

http://us.php.net/manual/en/function.mktime.php

 

That what you are lookin for?

[roopurt18]

What happens when you run a query such as:

 

select * from contacts where contact_date between '2010-04-10' and '2010-04-10 23:59:59'

 

Does it give you the contacts for that specific date?

[Jax2]

I tried doing this btw:

 

$now=date('Y-m-d');

$now2=time($now);

contact_next=($now+86400); (for tomorrow)

 

But unfortunately, it's still including the hours:minutes:seconds in that instead of just the date with a time of 12:00:00am

 

[Jax2]

What happens when you run a query such as:

 

select * from contacts where contact_date between '2010-04-10' and '2010-04-10 23:59:59'

 

Does it give you the contacts for that specific date?

 

It won't, because the contact_next field is stored as a timestampe (I.e. 1270837382), not a date (I.e. 2010-04-09)

 

[Jax2]

I guess what would solve this problem is to figure out how to create a timestamp for todays date at 12:00am --- so 2010-04-09:12:00.00 but in timestamp format (1270814400)

[roopurt18]

Really?  Because when I create a table with a timestamp column and select * in phpMyAdmin it's displaying them as '0000-00-00 00:00:00' and not as 0.

 

Anyways, if you look through the date and time functions for your version of MySQL, there should be a way to convert the timestamp to a '0000-00-00' day only format without having to change any of the column information or PHP code.

 

Something like:

select * from contacts where DATE_FORMAT( contact_date, 'format' ) between '2010-04-10' and '2010-04-10 23:59:59'

 

If DATE_FORMAT doesn't work there should be another function that will. 

[Jax2]

Okay. I found a solution.

 

Keep it simple. (Gee, such a great saying)...

 

Instead of messing around with conversions and trying to get this date to match that date out of a timestamp and BLA BLA BLA ... I simply added a new field:

 

call_back_date, which is the same thing as the timestamp but in date format, so then when I want to find out if a date is the same, I can just use THAT record instead of trying to convert the timestring to a date and then compare.

 

Problem solved

 

[Jax2]

Really?  Because when I create a table with a timestamp column and select * in phpMyAdmin it's displaying them as '0000-00-00 00:00:00' and not as 0.

 

Anyways, if you look through the date and time functions for your version of MySQL, there should be a way to convert the timestamp to a '0000-00-00' day only format without having to change any of the column information or PHP code.

 

Something like:

select * from contacts where DATE_FORMAT( contact_date, 'format' ) between '2010-04-10' and '2010-04-10 23:59:59'

 

If DATE_FORMAT doesn't work there should be another function that will. 

 

Heh, I'm not storing them the same way... mine are stored as: 1270814400 ... it's not an actual TIMESTAMP sql field ... it's just varchar with time() inserted...  Anyhow, I got it working, but I appreciate all your suggestions!!

[roopurt18]

Well if they're all varchar but with numeric timestamps in them, you should be able to do one of the following:

 

1) In the query, use a CAST() to convert the varchar to a timestamp.

 

2) Issue an alter table statement and change the column's type to timestamp.

 

I would strongly advise against leaving them as varchar as they are timestamps and the database has a built-in type to support that.