CODEWITHSUNDEEP
mysqlsqlmariadb
tomtom
tomtom

Reputation: 614

SQL Query help regarding filtering data

I wish to generate a list of selected column from table postion order by PositionDateTime(ie date ). but in that descending order wish to accept only date from particular year.

below sql query does shows data in descending order with reference to date .but i want to execute a sql query that filter out date ie not from current year (ie 2017 for now). eg i dont want to include date like 2018 and 2005 from below table. how can i execute such query. 

SELECT Lat, Lng, PositionDateTime FROM Position ORDER BY PositionDateTime DESC Limit 30 ;

current table result 

Lat     | Lng     | PositionDateTime    |
+---------+---------+---------------------+
| 47.1168 | 8.19226 | 2018-12-11 13:19:17 |
| 47.0996 | 7.77753 | 2018-12-11 12:55:29 |
| 47.0622 | 7.45892 | 2017-12-11 12:49:14 |
| 46.8484 | 7.33423 | 2017-12-11 12:48:10 |
| 47.2489 | 7.57043 | 2017-12-11 12:29:03 |
| 47.2899 | 8.25708 | 2017-12-11 11:37:41 |
|  47.409 | 7.77917 | 2017-12-11 10:57:42 |
| 47.3941 | 8.68005 | 2017-12-11 10:41:24 |
|       0 |       0 | 2017-12-11 10:39:12 |
|       0 |       0 | 2017-11-23 15:22:25 |
|       0 |       0 | 2017-11-23 12:32:58 |
|       0 |       0 | 2017-11-23 10:24:10 |
|       0 |       0 | 2017-11-20 09:29:36 |
|       0 |       0 | 2017-11-20 09:25:35 |
|       0 |       0 | 2017-11-20 09:25:18 |
| 47.5031 | 8.69181 | 2017-11-15 16:25:08 |
| 47.4799 | 8.48702 | 2005-11-15 16:24:41

Upvotes: 1

Views: 39

Answers (1)

Leopold Stotch
Leopold Stotch

Reputation: 1522

If you add YEAR(PositionDateTime) in the where clause, you can filter the results by the year:

SELECT Lat, Lng, PositionDateTime FROM Position 
WHERE YEAR(PositionDateTime) IN (2015,2017) #Enter the years you want here.
ORDER BY PositionDateTime DESC Limit 30 ;

Or to show only results for the current year just, try this:

SELECT Lat, Lng, PositionDateTime FROM Position 
WHERE YEAR(PositionDateTime) = YEAR(NOW()) 
ORDER BY PositionDateTime DESC Limit 30 ;

To show only results for the current year and a particular month, try this:

SELECT Lat, Lng, PositionDateTime FROM Position 
WHERE YEAR(PositionDateTime) = YEAR(NOW()) 
AND MONTH(PositionDateTime) = 6 # 6 = June, you can adjust this.
ORDER BY PositionDateTime DESC Limit 30 ;

Upvotes: 1

Related Questions