-3

I'm trying to select some rows and columns from bigquery data on help requests in NYC. I want to select five columns - date request created, city where the request was made, the agency that received the request, etc.

First, I managed to select the columns I want:

conn <- dbConnect(SQLite(),'nyc311.db')
dbListFields(conn, "requests")

df<-dbGetQuery(conn, 'SELECT "Agency", "Created Date", "Complaint Type", "City", Descriptor FROM requests)

  Agency           Created Date           Complaint Type     City                  Descriptor
1  DOHMH 01/25/2016 02:11:12 AM       Indoor Air Quality    BRONX Chemical Vapors/Gases/Odors
2   NYPD 01/25/2016 02:08:08 AM          Noise - Vehicle NEW YORK              Car/Truck Horn
3   NYPD 01/25/2016 02:07:24 AM  Noise - Street/Sidewalk NEW YORK                Loud Talking
4  CHALL 01/25/2016 02:05:00 AM    Opinion for the Mayor                              HOUSING
5    HRA 01/25/2016 02:01:46 AM Benefit Card Replacement                             Medicaid
6   NYPD 01/25/2016 01:54:56 AM         Blocked Driveway   CORONA                   No Access

How can I select from the .db file so that I get agency=NYPD, City=Bronx and Queens; and Created Date=year 2015? I tried the following but I am getting syntax errors.

df<-dbGetQuery(conn, 'SELECT "Agency", "Created Date", "Complaint Type", "City", Descriptor 
FROM requests WHERE City IN ("BRONX", "QUEENS") AND Agency="NYPD"
                   AND YEAR(Created Date)=2015')

I'm a beginner so I'm not clear about how to subset the year, since Created Date shows date and time in character format, not integer. I also noticed that the code runs except for the part YEAR(Created Date)=2015

halo09876
  • 2,725
  • 12
  • 51
  • 71

2 Answers2

0

There is no YEAR() function in SQLite (although MySQL has one, hence your confusion). First note that you are storing your dates as text, and also in a non ANSI format of mm/dd/yyyy. In order to compare the year of each record, you will have to extract this information using SQLite's string functions. The following should work:

SUBSTR("Created Date", 7, 4)

Note that you also need to place the Created Date column name in double quotes to escape whitespace.

Here is the actual query I would use:

SELECT "Agency",
       "Created Date",
       "Complaint Type",
       "City",
       "Descriptor"
FROM requests
WHERE City IN ('BRONX', 'QUEENS') AND
      Agency = 'DOHMH' AND
      SUBSTR("Created Date", 7, 4) = '2015'      -- compare against the string '2015'

Some notes: It is convention in SQL to use single quotes for string data. You may put all your column names in double quotes, but it is only necessary if you have whitespace, keywords, etc.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Hi, Thank you for the comment, I tried this in R but I get a message : no such function: YEAR – halo09876 Feb 08 '17 at 05:09
  • 1
    @TimBiegeleisen Using a substring to extract date information seems like it could get you into trouble. It would be safer to use `strftime` after converting "Created Date" into `datetime` – Chrisss Feb 08 '17 at 05:23
  • @TimBiegeleisen Thank you so much!! I'm running the code and it seems to be working, can I ask what 7,4 means? – halo09876 Feb 08 '17 at 05:24
  • `SUBSTR(col, 7, 4)` means take _4_ characters, starting in the _7th_ position of the string. This matches your created date string to extract the year. – Tim Biegeleisen Feb 08 '17 at 05:26
0

I think syntax error in created date Check this

'SELECT "Agency", "Created Date", "Complaint Type", "City", Descriptor 
FROM requests WHERE City IN ("BRONX", "QUEENS") AND Agency="DOHMH"
                   AND YEAR("Created Date")=2015'
AssenKhan
  • 576
  • 5
  • 15