Electronic – What do you call a switch/relay which switches on an appliance for a given set of time

I think your main problem is that you aren't assigning a variable or using an if statement with your digitalRead(PIR_override);, but even with that you may miss the button press in the loop. If it's a momentary switch then it goes high/low for only the time that it's pressed, so the processor would have to see this at the right time in the while loop. You could just hold the button down for about a second or you could connect the button to pin 2 or 3 and use an interrupt, which it looks like your button is connected to pin 3 according to your code. I think this is a better solution. In the embedded world interrupts are your friend.

So, according to the documentation (https://www.arduino.cc/en/Reference/AttachInterrupt), you could do something like this:

const byte PIR_override = 3;        // the number of the switch pin
volatile boolean override = false;          // Use this to know if your currently  in override mode or not when looking at the button press

Inside Setup() add:

pinMode(PIR_override, INPUT);
attachInterrupt(digitalPinToInterrupt(PIR_override), motionOverride, LOW); // the sense may need to be changed - from your image it looks like the button goes low when pressed

So now motionOverride will be called when the button is pressed. Just put what you want to happen inside this function. Something like this:

void motionOverride()
{
    if(override) // overriding motion and the button has been pressed - got to PIR mode
    {
        digitalWrite(overridestatus, LOW);  // turn override status LED OFF
        digitalWrite(relaypin, LOW);  // turn relay OFF - you may not want to do this... depends on what you want
        override = false;               // set the override flag
    }
    else // not overriding motion and the button has been pressed - go to override mode
    {
        digitalWrite(overridestatus, HIGH);  // turn override status LED ON
        digitalWrite(relaypin, HIGH);  // turn relay ON
        override = true;                // set the override flag
    }

   // I guess Arduino takes care of clearing the interrupt for you
}

Then inside your main loop() just look at the override flag (Note: I did not check any of your PIR motion detection logic, and hopefully I didn't mess up your brackets there):

void loop()
{
     if(!override)
     {
         val = digitalRead(inputPin);  // read PIR input value
         if (val == HIGH)  // check if the input is HIGH
         {           
              digitalWrite(relaypin, HIGH);  // turn LED ON
              if (pirState == LOW) 
              {
                   // we have just turned on
                   Serial.println("Motion detected!");
                   // We only want to print on the output change, not state
                   pirState = HIGH;
                   delay(DELVAR);    // maximum delay is 32776 millisecons,
                   //  delay(DELVAR);    // so add multiple delays together to get
                   // delay(DELVAR);    // so add multiple delays together to get
             }
        } 
        else
        {
            digitalWrite(relaypin, LOW); // turn LED OFF
            if (pirState == HIGH)
            {
                 // we have just turned of
                 Serial.println("Motion ended!");
                 // We only want to print on the output change, not state
                 pirState = LOW;
            } 
        }
     }
}

Try that out if you want. If there's a mistake I'll try and help with it.

If you had a resistor tied to the battery only, yes less current would be driven by that 1 MOhm resistor, but actually less heat is generated. Heat is generated from the power dissipated (as heat) by the part. So power is V * I which is (for a one resistor, one 12 V battery setup) 12 V * (12 V / Resistance) using I = V/R to calculate I. So as resistance goes up, power goes down for a constant voltage.

I don't know enough to reliably help you with your main problem, just thought I would contribute what I could.