Timestamp SQL date field with PHP time

[scoutch29]

Hi,

I have a small problem when comparing two dates (from and to) with each of those SQL fields (date_from, date_to) with the PHP time() function.

I would like the from date not to be lower than the microtime date specified and the to date not to exceed the current time. Additionally, I have a new date period which must not exceed the to date either (let's say: new_date).

Would someone kindly shed me some lights on this ? 🙂

 

[scoutch29]

Anyone ?

 

[KIAman]

Are you looking to make the comparison inside transact SQL or PHP? Can you provide some code so we can see what you are working with?

 

[scoutch29]

Hi, I'm looking for to compare between SQL fields from PHP. I do not have the codes, which is why I ask how it could be done ...

example:

SQL Fields:

cutoff
new_image_date
available_from_date
available_to_date

Now, the objective is to not exceed the available_to_date field period (microtime period) even though the new_image_date time range has been exceeded in days. The available_from_date would, then, take the new_image_date's place until the available_from_date reaches, as well, the available_to_date the same way the new_image_date field has reached its time period from the cutoff field. The cutoff field would be determined like this for the new_image_date time period:

time() - 60 * 60 * 24 * cutoff;

which the 'cutoff' would be used with the object in this case. I do know how to retrieve new image time period but the rest explained on the above, I'm unable to.

Any help would be appreciated on this. 🙂

 

[Kr@n]

If I understand your problem well, you could always code your SQL query for it to return normalized strings (ODBC : yyyy-mm-dd hh:mi:ss.mmm or ISO : yyyy-mm-ddThh:mi:ss.mmm) that you would parse in PHP code in order to convert them back to timestamps.

This would also ensure database agnosticism (no datetime to int to timestamp trickery or whatever) ...

Another solution would be to use a PHP database connector (middleware) that does it for you (like AdoDB IIRC)


However, I think it may be a clever idea to provide your query the needed parameters (cutoff ?) and calculate the resulting dates in SQL ...

Hope That Helps ...


NOTE : I'm not sure I answered your question, because I have a feeling you are implying you cannot modify the SQL queries (have no access to them).Is that so ?

 

[scoutch29]

If I understand your problem well, you could always code your SQL query for it to return normalized strings (ODBC : yyyy-mm-dd hh:mi:ss.mmm or ISO : yyyy-mm-ddThh:mi:ss.mmm) that you would parse in PHP code in order to convert them back to timestamps.

This would also ensure database agnosticism (no datetime to int to timestamp trickery or whatever) ...

Another solution would be to use a PHP database connector (middleware) that does it for you (like AdoDB IIRC)


However, I think it may be a clever idea to provide your query the needed parameters (cutoff ?) and calculate the resulting dates in SQL ...

Hope That Helps ...


NOTE : I'm not sure I answered your question, because I have a feeling you are implying you cannot modify the SQL queries (have no access to them).Is that so ?

The cutoff means the maximum value in days when the time expires. I'm only looking for to do this in mySQL. As for the access, no actually ... I do have access but I just cannot apply the query as expected and simply looking for a way to achieve this ...

As for the date format, it's yyyy-mm-dd. As explained on my first post, the specified date fields (from, to and new date) are already timestamped fields. There's no need to convert them back to timestamps ...

Any solution (code) of what it could be ? 🙂

 

[Kr@n]

If all of your data is available in your query (all data is in fields), then it is quite trivial to calculate the needed values in SQL.

Alas, I do not really know what you want to do, and thus cannot provide you specific code snippets. What is the difficulty : adding your (integer ?) offset to your timestamps, comparing your timestamps (I do not know MySQL very well, but I'm pretty sure some case ... when and < > operators would do the trick).

If you still need some help, can you please provide the SQL types of the involved fields (the CREATE TABLE script for the said table will do), and describe your calculation (in pseudo-code / math) ?


Always useful : http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

Hope That Helps ...