Select first day and last day of previous month sqlplus unixtimestamp











up vote
0
down vote

favorite












I want to extract the whole last month data using sqlplus, based on a column that is unixtimestamp, and I am not able to find the correct query. Using 



unixtimestamp_column >= to_number((add_months(sysdate,-1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000";


it outputs from the day when the script is run due do the sysdate used. I want to be able to extract from the first day 00:00:00 of the previous month until the last day 23:59:59 of the previous month. Also, the user should not pass any kind of parameter or argument.






solaris sqlplus







share|improve this question
























  • Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
    – fra-san
    Nov 27 at 10:07












  • I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
    – bboy
    Nov 27 at 10:14








  • 1




    I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
    – fra-san
    Nov 27 at 14:04








  • 1




    Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
    – fra-san
    Nov 27 at 14:14






  • 3




    I'm voting to close this question as off-topic because it's about SQL
    – roaima
    Nov 28 at 8:54















up vote
0
down vote

favorite












I want to extract the whole last month data using sqlplus, based on a column that is unixtimestamp, and I am not able to find the correct query. Using 



unixtimestamp_column >= to_number((add_months(sysdate,-1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000";


it outputs from the day when the script is run due do the sysdate used. I want to be able to extract from the first day 00:00:00 of the previous month until the last day 23:59:59 of the previous month. Also, the user should not pass any kind of parameter or argument.






solaris sqlplus







share|improve this question
























  • Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
    – fra-san
    Nov 27 at 10:07












  • I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
    – bboy
    Nov 27 at 10:14








  • 1




    I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
    – fra-san
    Nov 27 at 14:04








  • 1




    Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
    – fra-san
    Nov 27 at 14:14






  • 3




    I'm voting to close this question as off-topic because it's about SQL
    – roaima
    Nov 28 at 8:54













up vote
0
down vote

favorite









up vote
0
down vote

favorite











I want to extract the whole last month data using sqlplus, based on a column that is unixtimestamp, and I am not able to find the correct query. Using 



unixtimestamp_column >= to_number((add_months(sysdate,-1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000";


it outputs from the day when the script is run due do the sysdate used. I want to be able to extract from the first day 00:00:00 of the previous month until the last day 23:59:59 of the previous month. Also, the user should not pass any kind of parameter or argument.






solaris sqlplus







share|improve this question















I want to extract the whole last month data using sqlplus, based on a column that is unixtimestamp, and I am not able to find the correct query. Using 



unixtimestamp_column >= to_number((add_months(sysdate,-1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000";


it outputs from the day when the script is run due do the sysdate used. I want to be able to extract from the first day 00:00:00 of the previous month until the last day 23:59:59 of the previous month. Also, the user should not pass any kind of parameter or argument.






solaris sqlplus




solaris sqlplus






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 27 at 10:41









fra-san

1,106214




1,106214










asked Nov 27 at 8:58









bboy

274




274












  • Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
    – fra-san
    Nov 27 at 10:07












  • I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
    – bboy
    Nov 27 at 10:14








  • 1




    I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
    – fra-san
    Nov 27 at 14:04








  • 1




    Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
    – fra-san
    Nov 27 at 14:14






  • 3




    I'm voting to close this question as off-topic because it's about SQL
    – roaima
    Nov 28 at 8:54


















  • Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
    – fra-san
    Nov 27 at 10:07












  • I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
    – bboy
    Nov 27 at 10:14








  • 1




    I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
    – fra-san
    Nov 27 at 14:04








  • 1




    Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
    – fra-san
    Nov 27 at 14:14






  • 3




    I'm voting to close this question as off-topic because it's about SQL
    – roaima
    Nov 28 at 8:54
















Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
– fra-san
Nov 27 at 10:07






Welcome to U&L! It looks like your question is specific to Oracle Database/tools, so it's probably off-topic here. You might want to ask for it being migrated, possibly to StackOverflow. As for your question, you probably need to use TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1 as the base date for the starting point of your time interval (instead of add_months(sysdate,-1)), and TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) as it's end point. Such an interval should be closed to the left (>=), open to the right (<).
– fra-san
Nov 27 at 10:07














I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
– bboy
Nov 27 at 10:14






I used : unixtimestamp_column >= TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60) * 1000"; and got identifier is too long .
– bboy
Nov 27 at 10:14






1




1




I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
– fra-san
Nov 27 at 14:04






I think what you need is something like unixtimestamp_column >= (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 and unixtimestamp_column < (TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))+1) - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000
– fra-san
Nov 27 at 14:04






1




1




Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
– fra-san
Nov 27 at 14:14




Note that Unix timestamps are in UTC, while Oracle date functions output local times. This may be relevant to your application.
– fra-san
Nov 27 at 14:14




3




3




I'm voting to close this question as off-topic because it's about SQL
– roaima
Nov 28 at 8:54




I'm voting to close this question as off-topic because it's about SQL
– roaima
Nov 28 at 8:54















active

oldest

votes











Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "106"
};
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',
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f484400%2fselect-first-day-and-last-day-of-previous-month-sqlplus-unixtimestamp%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes
















draft saved

draft discarded




















































Thanks for contributing an answer to Unix & Linux Stack Exchange!


  • 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.





Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


Please pay close attention to the following guidance:


  • 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.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f484400%2fselect-first-day-and-last-day-of-previous-month-sqlplus-unixtimestamp%23new-answer', 'question_page');
}
);

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







-

Popular posts from this blog

Morgemoulin

Image
Morgemoulin — miejscowość i gmina — Państwo   Francja Region Grand Est Departament Moza Okręg Verdun Kod INSEE 55357 Powierzchnia 6,82 km² Populacja  (1990) • liczba ludności 75 • gęstość 11 os./km² Kod pocztowy 55400 Położenie na mapie Francji Morgemoulin 49°14′N   5°35′E / 49,233333   5,583333 Portal Francja Morgemoulin – miejscowość i gmina we Francji, w regionie Grand Est, w departamencie Moza. Według danych na rok 1990 gminę zamieszkiwało 75 osób, a gęstość zaludnienia wynosiła 11 osób/km² (wśród 2335 gmin Lotaryngii Morgemoulin plasuje się na 970. miejscu pod względem liczby ludności, natomiast pod względem powierzchni na miejscu 848.). Bibliografia | Francuski urząd statystyczny ( fr. ) . p   •   d   •   e Gminy okręgu Verdun Abaucourt-Hautecourt • Aincreville • Ambly-sur-Meuse • Amel-sur-l'Étang • Ancemont • Arrancy-sur-Crusne • Aubréville • Autré...
Read more

Scott Moir

Image
Scott Moir Tessa Virtue i Scott Moir w 2016 r. Reprezentacja   Kanada Data i miejsce urodzenia 2 września 1987 London Wzrost 180 cm Konkurencja Pary taneczne Partner sportowy Tessa Virtue Trener Marie-France Dubreuil, Patrice Lauzon, Romain Haguenauer Poprzednio: Marina Zujewa, Oleg Epstein, Igor Szpilband, Johnny Johns, Carol Moir, Paul MacIntosh, Suzanne Killing Klub Montreal International School of Skating Poprzednio: Arctic Edge FSC Ilderton Skating Club Lokalizacja treningowa Montreal Poprzednio: Canton Kitchener Rekordy życiowe ISU przed sezonem 2018/2019 Nota łączna 206,07 Igrzyska Olimpijskie 2018 Taniec krótki 83,67 Igrzyska Olimpijskie 2018 Taniec dowolny 122,40 Igrzyska Olimpijskie 2018 Dorobek medalowy Reprezentacja   Kanada Igrzyska olimpijskie Złoto Vancouver 2010 pary taneczne Złoto Pjongczang 2018 pary taneczne Złoto Pjongczang 2018 drużynowo Srebro S...
Read more

Souastre

Image
Souastre — miejscowość i gmina — Herb Państwo   Francja Region Hauts-de-France Departament Pas-de-Calais Okręg Arras Kod INSEE 62800 Powierzchnia 7,18 km² Populacja  (1990) • liczba ludności 352 • gęstość 49 os./km² Kod pocztowy 62111 Położenie na mapie Francji Souastre 50°09′N   2°34′E / 50,150000   2,566667 Portal Francja Souastre – miejscowość i gmina we Francji, w regionie Hauts-de-France, w departamencie Pas-de-Calais. Według danych na rok 1990 gminę zamieszkiwały 352 osoby, a gęstość zaludnienia wynosiła 49 osób/km² (wśród 1549 gmin regionu Nord-Pas-de-Calais Souastre plasuje się na 887. miejscu pod względem liczby ludności, natomiast pod względem powierzchni na miejscu 507.). Bibliografia | Francuski urząd statystyczny ( fr. ) . p   •   d   •   e Gminy okręgu Arras Ablain-Saint-Nazaire Ablainzevelle Acheville Achicourt Achiet-le-Grand Achiet-le-Pe...
Read more