Check if unix timestamp is within the current month in MySQL

strtotime
php date
php current timestamp
php unix timestamp to date
strtotime online
php date to timestamp
strtodate
php time to string

I have records of questions stored in database. One of the column in question is the ask_date.

Example stored in is: 1549923808.

I want to select records whose ask_date is within the current month.

Please how do I calculate that in the WHERE clause of my SQL ?

//Select questions with the heighest or Top votes or answers this month( current month)
        $SQL = "SELECT * FROM(
        SELECT Q.*, 
            (SELECT COUNT(Q.question_id) FROM $questions_table Q ) num_of_rows,
          (SELECT COUNT(v.vote_id) FROM $votes_table v WHERE Q.question_id=v.ask_id AND v.vote_type=0 ) votes_down,
          (SELECT COUNT(a.answer_id) FROM $answers_table a WHERE question_id=a.ask_id ) total_answers,
          (SELECT COUNT(v.vote_id) FROM $votes_table v WHERE Q.question_id=v.ask_id ) votes_up,
          CONCAT(m.firstname,' ',m.lastname) author_name,
          m.username u_name FROM $questions_table Q 
          LEFT JOIN $main_table m ON Q.user_id=m.user_id
          WHERE ............

          ) A LEFT JOIN $votes_table V ON A.question_id=V.ask_id 
       GROUP BY A.question_id ORDER BY (
           SELECT COUNT(V.vote_id) vote 
           FROM $votes_table V 
           WHERE V.ask_id=A.question_id
       ) DESC LIMIT  {$maxPageRecords} OFFSET {$offset}";

A simple way is:

where date_format(from_unixtime(ask_date), '%Y-%m') = date_format(now(), '%Y-%m')

However, that precludes the use of indexes. So, this might be better:

where ask_date >= unix_timestamp(date(concat_ws('-', year(now()), month(now()), 1)))) and
      ask_date < unix_timestamp(date(concat_ws('-', year(now()), month(now()), 1))) + interval 1 month)

How to Query Date and Time in MySQL, Works for PostgreSQL, MySQL, BigQuery, SQL Server, Redshift, Snowflake, SQLite, Presto, MySQL has the following functions to get the current date and time: To find rows created within the last week: To calculate the difference between two timestamps, convert them to unix timestamps then perform the subtraction:. UNIX_TIMESTAMP() function. MySQL UNIX_TIMESTAMP() returns a Unix timestamp in seconds since '1970-01-01 00:00:00' UTC as an unsigned integer if no arguments are passed with UNIX_TIMESTAMP(). When this function used with a date argument, it returns the value of the argument as an unsigned integer in seconds since '1970-01-01 00:00:00' UTC.

select records whose ask_date is within the current month :

This simple (and index-friendly) expression should get the job done :

WHERE ask_date >= UNIX_TIMESTAMP(DATE_FORMAT(NOW(), '%Y-%m-01'))

Impala Date and Time Functions | 5.8.x, Deploying Clients � Testing the Installation � Uninstalling Cloudera Manager Like all date/time functions that deal with months, date_add() handles The time portions of the TIMESTAMP values are irrelevant. from_unixtime(bigint unixtime[, string format]): Purpose: Converts the number of seconds from the Unix epoch to� To convert a timestamp to a unix timestamp (integer seconds): -- This will assume time to be 12am SELECT unix_timestamp ( '2018-12-09' ); -- You can specify an exact timestamp to be converted down to the second SELECT unix_timestamp ( '2018-12-09 14:53:21' ); -- calling unix_timestamp without a parameter will be like calling it for current

Using SQL Tuples: WHERE (year(ask_date),month(ask_date)) = (year(now()),month(now()))

PHP strtotime() Function, Parse English textual datetimes into Unix timestamps: if requesting a given occurrence of a given weekday in a month where that weekday was the first day of� How can I select Current Month records from a table of MySql database?? Like now current month is January. I would like to get records of January Month, Where data type of my table column is timestamp.I would like to know the sql query. Thanks

strtotime - Manual, The Unix timestamp that this function returns does not contain information about time zones. previous to PHP 5.1.0 you would compare with -1, instead of false If the number of the year is specified in a two digit format, the values between 00-69 are month differently than other implementations like for example MySQL. The Mini-Course. Let's first create a simple logging-table and some sample records. All queries on this page will work with the following table.-- Table "mytable" DDL CREATE TABLE `mytable` ( `userId` int(11) NOT NULL, `url` varchar(100) NOT NULL, `epoch` int(11) NOT NULL ); INSERT INTO mytable VALUES(1,'homepage',1225540800); -- User 1 visited the url homepage on Nov 1, 2008 INSERT INTO

12.6 Date and Time Functions, FROM_UNIXTIME(), Format Unix timestamp as a date LAST_DAY, Return the last day of the month for the argument When invoked with the days form of the second argument, MySQL treats it as an integer number of days to be Range checking on the parts of date values is as described in Section 11.2.2, “The DATE ,� i have a query in MySQL which serves me very well by getting all the records within the current month; SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH); The above query works well. so if this month we only had two records and 28 days it will bring only two records.

Using Dates and Timestamps — Snowflake Documentation, If a set of incoming timestamps has no time zone specified, then Snowflake loads with some other systems, the dayofweek part follows the UNIX standard. next month, and then subtracts 1 day to determine the last day of the current month. Definition and Usage. The MONTH() function returns the month part for a given date (a number from 1 to 12). Syntax

Comments
  • If ask_date is datetime or timestamp, wouldn't leave out '2019-02-28 23:59:59' since between's right value would be '2019-02-28 00:00:00' ?
  • @Sal . . . Don't use between with dates or date/times. Simple comparisons are more accurate.
  • My bad, I meant: second operand of second comparison.
  • @Sal . . . Not at all. The < does what the OP wants.
  • it gives me this error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'month(now()) ) A LEFT JOIN votes V ON A.question_id=V.ask_id G' at line 10
  • You need to split up the conditions he put there so that you compare the years, then compare the months