The convolution $G_{ret}*f$ of the retarded propagator $G_{ret}$ with a source term $f$ which vanishes sufficiently far in the past is the unique solution of the inhomogeneous Klein-Gordon equation with source term $f$ which vanishes in the far past. It is necessarily a nontrivial superposition of positive and negative energy solutions at all times when it is not identically zero, precisely because it vanishes outside the future light cone of the support of $f$.
The convolution $G_F*f$ of the Feynman propagator $G_F$ with a source term $f$ which vanishes outside a bounded space-time region, on its turn, is a solution of the inhomogeneous Klein-Gordon equation which is of positive energy outside the past light cone of the support of $f$ (i.e. in the far future) and of negative energy outside the future light cone of the support of $f$ (i.e. in the far past). Because of this, it cannot vanish in any nonvoid open region of space-time, unlike $G_{ret}*f$.
The relation between the position-space support and the momentum-space support of $G_{ret}$ and $G_F$ can be seen as a consequence of the uncertainty principle, which restricts the regions where a distribution can vanish in position and momentum space (the latter after a Fourier transform).
Well, you asked for a start, not a nice operator—but I suspect some coherent state/displacement operator review or paper has it nicely. I hasten to frame the issue, and you might choose to carry it out more efficiently.
You are seeking a representation-changing operator $\hat{\mathcal{O}}(x)$ taking you from Fock states (eigenstates of the number/energy operator) to eigenstates of the position operator $\hat{X}$ with eigenvalue x. The operator will be a function of x and creation operators.
I use the conventions of Sakurai & Napolitano, QM, (2.3.21),
$$
|n\rangle= \frac{a^{\dagger ~n}}{\sqrt{n!}} |0\rangle
$$
and, significantly, since you are using the entire Fock space,(2.3.32),
$$
\langle n| x \rangle= \frac{1}{\pi^{1/4}\sqrt{2^n~n!}}~ (x-\partial_x)^n~ e^{-x^2/2} ~.
$$
You then posit your basis change,
$$
|x\rangle= \hat{\mathcal{O}}(x) |0\rangle= \sum_0^\infty c_n (x) |n\rangle,
$$
and use the above relation, as you did for n=0 to determine the coefficients c(x).
The good news is that the answer,
$$
|x\rangle= \sum_0^\infty \frac{1}{\pi^{1/4}} ~ \frac {(x-\partial_x)^n a^{\dagger ~n}}{2^{n/2} n!} e^{-x^2/2 }|0\rangle
$$
exponentiates, as often,
$$
\hat{\mathcal{O}}(x)= \frac{1}{\pi^{1/4}} e^{(x-\partial_x)~ a^\dagger /\sqrt{2} } ~ e^{-x^2/2 },
$$ and the exponential of operators is straightforward to split by use of the degenerate CBH identity, since $[x,-\partial] =1$, so $\exp(-a^{\dagger ~2}/4) \exp(xa^\dagger/\sqrt{2})\exp(-a^\dagger \partial_x /\sqrt{2})$. And hence you get the exponential of the gradient ∂ on the right, thus a translation operator:
it translates the exponent of the x-Gaussian to $-(x-a^\dagger/\sqrt{2})^2/2$.
So, finally,
$$
\bbox[yellow,5px]{\hat{\mathcal{O}}(x)= \frac{1}{\pi^{1/4}} e^{-x^2/2} e^{\sqrt{2} xa^\dagger} e^{-a^{\dagger ~2}/2} =\frac{e^{x^2/2}}{\pi^{1/4}} e^{-(a^\dagger-\sqrt{2} x)^2/2} } .
$$
As a lark, you may check that, since $a$ acts as a derivation w.r.t. $a^\dagger$,
$$
\frac{(a+a^\dagger)}{\sqrt 2} ~\hat{\mathcal{O}}(x) |0\rangle= x~\hat{\mathcal{O}}(x) |0\rangle ~,
$$
so an eigenstate of $\hat{X}$, indeed.
I have not checked the delta-orthogonality of $\langle y|x\rangle$ states here.
Edit: I found out today that this is but Prob. 14.4.a) of M Schwartz's book on QFT. In any case, working out the <x|p> in 312004 yields the plane wave, and inserting complete momentum states and integrating over them trivially produces the δ-function normalization $\langle y| x\rangle\propto \delta(x-y)$ sought.
Edit II: This is, in fact, reducible to the celebrated Segal-Bargmann transform, Def 2 & Corollary 1, should you wish to pursue it more formally and stick labels on it.
Edit III : I am being repeatedly asked about the connection of this oscillator vacuum $|0\rangle$ to the translationally invariant vacuum of Dirac's book, the awesome standard ket, $~\lim_{p\to 0} |p\rangle\equiv |\varpi\rangle ~$, for which $~\hat{p} |\varpi \rangle =0~$ and $~\langle x | \varpi\rangle=1/\sqrt{2\pi \hbar}~$, as well as $~\langle x | \hat{x}|\varpi\rangle=x/\sqrt{2\pi \hbar}$.
The relation is actually $$|\varpi\rangle= \exp (a^\dagger a^\dagger /2) |0\rangle {1\over (\pi \hbar)^{1/4}}~,$$ implicit in the above, but evident in this obscurely placed answer. By inspection, this state is in the kernel of $\sqrt{2} \hat{p}=a-a^\dagger$.
Best Answer
You've motivated me to read the nice development in Mukhanov and think a little about this issue. Here is how I rationalized it to myself:
I think you should consider the fundamental definition of the Feynman propagator not as a propagator with a particular contour choice, but as an propagator that is symmetric under exchange of spacetime coordinates. In this zero-dimensional context, that means exchange of $t$ and $t'$. In QFT language this leads to a structure like:
$$ -i \langle 0 | \Theta(t-t')\psi(t)\psi(t')+ \Theta(t'-t)\psi(t')\psi(t)|0\rangle $$ While in the driven oscillator case the form is: $$\frac{i}{2\omega} \left(e^{-i\omega(t-t')}\Theta(t-t')+e^{-i\omega(t'-t)}\Theta(t'-t)\right)$$
In either case, you can Fourier transform this and get something with one pole above and one pole below the complex $\omega$ plane, from the two Heaviside functions. But since we are supposed to be looking for a physical intuition, I suggest you think of this as a consequence of demanding that the propagator be symmetric under exchange of times.
As far as a physical interpretation, I think you will just have to accept that the interpretation isn't going to be as straightforward in the case of the Feynman propagator as it is for the retarded propagator. Your question already sets up why this is the case: one is an expectation value, which is something that appears classically and that we have a natural feel for, while the other is an off-diagonal matrix element. The good news is that by this point in our study of quantum mechanics, we've both seen enough of these to have some sense of what they mean. For example, an amplitude of the form $\langle 0_{out}|\hat{q} |0_{in}\rangle$ is well-known in atomic physics as the dipole operator. Roughly speaking, it represents the degree to which the states $|0_{out}\rangle$ and $|0_{in}\rangle$ are coupled by the operator $J \hat{q}$. Summarizing, then, the best interpretation I can offer for the Feynman propagator in this situation is that it is the object which tells you, for a given transient current, the extent to which the resulting output state is coupled to the initial state. So it is a particular, and calculationally important, way of characterizing how much the states were changed by the driving force. Since it is not itself an observable, it is not clear to me that a more intuitive description should exist or what it might entail.