SOQL Socrata query datetime between
May I know what is wrong with my below code ? I would like to query all where date_occ is between '2015-01-10T12:00:00' and '2015-12-31T24:00:00'
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&$where = date_occ between 2015-01-10T12:00:00 and 2015-12-131T24:00:00')
I get the following error:
Unrecognized arguments [$where ]
I realise the following doesn't work as well
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_age >20')
data = response.json()
data = json_normalize(data)
data = pd.DataFrame(data)
But this works:
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_sex=M')
what am I missing here?
python api socrata soda opendata
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
add a comment |
May I know what is wrong with my below code ? I would like to query all where date_occ is between '2015-01-10T12:00:00' and '2015-12-31T24:00:00'
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&$where = date_occ between 2015-01-10T12:00:00 and 2015-12-131T24:00:00')
I get the following error:
Unrecognized arguments [$where ]
I realise the following doesn't work as well
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_age >20')
data = response.json()
data = json_normalize(data)
data = pd.DataFrame(data)
But this works:
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_sex=M')
what am I missing here?
python api socrata soda opendata
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
add a comment |
May I know what is wrong with my below code ? I would like to query all where date_occ is between '2015-01-10T12:00:00' and '2015-12-31T24:00:00'
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&$where = date_occ between 2015-01-10T12:00:00 and 2015-12-131T24:00:00')
I get the following error:
Unrecognized arguments [$where ]
I realise the following doesn't work as well
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_age >20')
data = response.json()
data = json_normalize(data)
data = pd.DataFrame(data)
But this works:
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_sex=M')
what am I missing here?
python api socrata soda opendata
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
May I know what is wrong with my below code ? I would like to query all where date_occ is between '2015-01-10T12:00:00' and '2015-12-31T24:00:00'
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&$where = date_occ between 2015-01-10T12:00:00 and 2015-12-131T24:00:00')
I get the following error:
Unrecognized arguments [$where ]
I realise the following doesn't work as well
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_age >20')
data = response.json()
data = json_normalize(data)
data = pd.DataFrame(data)
But this works:
response = requests.get('https://data.lacity.org/api/id/7fvc-faax.json?$select=*&vict_sex=M')
what am I missing here?
python api socrata soda opendata
python api socrata soda opendata
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
edited Nov 26 '18 at 9:00
Tom Schenk Jr
1053
1053
asked Nov 25 '18 at 23:05
judeboxjudebox
175
asked Nov 25 '18 at 23:05
judeboxjudebox
175
asked Nov 25 '18 at 23:05
judeboxjudebox
175
175
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There are a few questions and answers posed in this one. Starting with your second query first; where you want to look at age above 20 years-old. Looking at the metadata (click the down arrow), the victim age is not numeric and is a text string. Thus, you won't be able to use operators like greater than, less than, etc. However, you can look for "equal to". The query below will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=vict_age = '20'
Note: I've dropped the $select and am just using $where for simpler display.
Your third example works since you've set it to query a text field. If you want LA to change it to a numeric, click the "Contact Dataset Owner" under the ellipsis button.
Your first question on dates has a few changes. First, your single quotation marks were not aligned and some were missing. Second, the latter date is 2015-12-131T24:00:00, which has an invalid day. Finally, the data on the portal does not have a timestamp, so you only need the year-month-day. This will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=date_occ between '2015-01-10' and '2015-12-13'
Finally, I would recommend that you use the URL structure, https://data.lacity.org/resource/7fvc-faax.json? instead of /api/id/. The former is the proper URL structure for Socrata-based APIs.
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472902%2fsoql-socrata-query-datetime-between%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There are a few questions and answers posed in this one. Starting with your second query first; where you want to look at age above 20 years-old. Looking at the metadata (click the down arrow), the victim age is not numeric and is a text string. Thus, you won't be able to use operators like greater than, less than, etc. However, you can look for "equal to". The query below will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=vict_age = '20'
Note: I've dropped the $select and am just using $where for simpler display.
Your third example works since you've set it to query a text field. If you want LA to change it to a numeric, click the "Contact Dataset Owner" under the ellipsis button.
Your first question on dates has a few changes. First, your single quotation marks were not aligned and some were missing. Second, the latter date is 2015-12-131T24:00:00, which has an invalid day. Finally, the data on the portal does not have a timestamp, so you only need the year-month-day. This will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=date_occ between '2015-01-10' and '2015-12-13'
Finally, I would recommend that you use the URL structure, https://data.lacity.org/resource/7fvc-faax.json? instead of /api/id/. The former is the proper URL structure for Socrata-based APIs.
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
add a comment |
There are a few questions and answers posed in this one. Starting with your second query first; where you want to look at age above 20 years-old. Looking at the metadata (click the down arrow), the victim age is not numeric and is a text string. Thus, you won't be able to use operators like greater than, less than, etc. However, you can look for "equal to". The query below will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=vict_age = '20'
Note: I've dropped the $select and am just using $where for simpler display.
Your third example works since you've set it to query a text field. If you want LA to change it to a numeric, click the "Contact Dataset Owner" under the ellipsis button.
Your first question on dates has a few changes. First, your single quotation marks were not aligned and some were missing. Second, the latter date is 2015-12-131T24:00:00, which has an invalid day. Finally, the data on the portal does not have a timestamp, so you only need the year-month-day. This will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=date_occ between '2015-01-10' and '2015-12-13'
Finally, I would recommend that you use the URL structure, https://data.lacity.org/resource/7fvc-faax.json? instead of /api/id/. The former is the proper URL structure for Socrata-based APIs.
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
add a comment |
There are a few questions and answers posed in this one. Starting with your second query first; where you want to look at age above 20 years-old. Looking at the metadata (click the down arrow), the victim age is not numeric and is a text string. Thus, you won't be able to use operators like greater than, less than, etc. However, you can look for "equal to". The query below will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=vict_age = '20'
Note: I've dropped the $select and am just using $where for simpler display.
Your third example works since you've set it to query a text field. If you want LA to change it to a numeric, click the "Contact Dataset Owner" under the ellipsis button.
Your first question on dates has a few changes. First, your single quotation marks were not aligned and some were missing. Second, the latter date is 2015-12-131T24:00:00, which has an invalid day. Finally, the data on the portal does not have a timestamp, so you only need the year-month-day. This will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=date_occ between '2015-01-10' and '2015-12-13'
Finally, I would recommend that you use the URL structure, https://data.lacity.org/resource/7fvc-faax.json? instead of /api/id/. The former is the proper URL structure for Socrata-based APIs.
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
There are a few questions and answers posed in this one. Starting with your second query first; where you want to look at age above 20 years-old. Looking at the metadata (click the down arrow), the victim age is not numeric and is a text string. Thus, you won't be able to use operators like greater than, less than, etc. However, you can look for "equal to". The query below will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=vict_age = '20'
Note: I've dropped the $select and am just using $where for simpler display.
Your third example works since you've set it to query a text field. If you want LA to change it to a numeric, click the "Contact Dataset Owner" under the ellipsis button.
Your first question on dates has a few changes. First, your single quotation marks were not aligned and some were missing. Second, the latter date is 2015-12-131T24:00:00, which has an invalid day. Finally, the data on the portal does not have a timestamp, so you only need the year-month-day. This will work:
https://data.lacity.org/resource/7fvc-faax.json?$where=date_occ between '2015-01-10' and '2015-12-13'
Finally, I would recommend that you use the URL structure, https://data.lacity.org/resource/7fvc-faax.json? instead of /api/id/. The former is the proper URL structure for Socrata-based APIs.
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
answered Nov 26 '18 at 5:35
Tom Schenk JrTom Schenk Jr
1053
1053
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
add a comment |
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
that make a lot of sense. I've totally missed out the type of each column in the data. thanks for pointing that out. I've amended the quotation marks and now it works ! response = requests.get('data.lacity.org/resource/7fvc-faax.json?$where=date_occ between "2015-01-10" and "2015-12-13"'). thanks again!
– judebox
Nov 26 '18 at 11:08
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472902%2fsoql-socrata-query-datetime-between%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
