Working with dates in SQL, PHP

I need to make a query to the database, tell me which of the two types of queries will be better.

1) Generate date in php and just pass it to sql (field type date_endtimestamp)

<?php
mysql_query("SELECT*FROM WHERE ".time()." <= `date_end`"); 
?>

2) Let’s use the built-in function

<?php
mysql_query("SELECT*FROM WHERE NOW()<=`date_end`"); 
?>

And I would like to know in what format it is better to store the date in the database in datetime or timestamp


Answer 1, authority 100%

time()gives the time in seconds since the start of the unix epoch, accordingly, it is not a fact that even two consecutive calls to the time function will give the same result, so talking about query caching (as suggested by @draev) is meaningless if only you will not fix the result of time()(save it in a variable) at the beginning of the script and will not use it everywhere.

Personally, my IMHO is to use data substitution in SQL query only where it is really needed, so in all projects I try to use native mysql tools for working with dates and times. In addition, later it is easier to check them outside of php and edit the data in the table manually (the data format of the field in the table corresponds to the format returned by the standard mysql function).


Answer 2, authority 50%

  1. this variant will be cached, the fetch rate will be faster
  2. mysql does not cache queries that use functions related to the current time (NOW(), CURDATE(), etc.)

You can store the date in any format you like.


Answer 3

For format selection, read here (in English)

In short:
If you need to store the date / time of messages on the forum – you can choose “to taste”
If you need to record the dates of historical events, TIMESTAMP will not help (from ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC), choose DATETIME – it allows you to fix the date /time between ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’

Besides the ranges, TIMESTAMP has one more difference – it “auto-updates according to the client’s time zone” – i.e. if in Moscow a message was marked as written at 3 o’clock, in London the client will see that it was written at 0 o’clock GMT…