0

I have a TIMESTAMP WITHOUT TIME ZONE column in PostgreSQL lets call it last_update. I discovered that when I query a field that has not been updated for 6 months it is not found ie.

SELECT id FROM example WHERE last_update = '2015-03-29 03:36:31.012000';

and if I do

SELECT last_update FROM example WHERE id = 1234;

the result is 2015-03-29 03:36:31.012000 so i know the timestamp should be found.

The

SELECT id FROM example WHERE last_update = '2015-10-21 10:15:00.468000';

query works for example, its just the entries that have not been updated since exactly the 29th of march that cannot be found with select.

Does anyone have an idea why this is happening and how can i fix it? Could it be that the database is corrupt?

Improve this question
5
  • Did you refer this: stackoverflow.com/questions/21277170/… ? Commented Oct 21, 2015 at 9:27
  • Could be caused the implicit data type conversion. Did you try: where last_update = timestamp '2015-03-29 03:36:31.012000' Commented Oct 21, 2015 at 11:17
  • And what is your Postgres version ? (before 8.4 ?) Commented Oct 21, 2015 at 11:33
  • hi, my postgres version is 9.3.9 the following works select id from example where last_update = '2015-03-29 02:36:31.012000'; but the following does not work select id from example where last_update = timestamp '2015-03-29 03:36:31.012000'; so it is a timestamp problem i just cannot figure out why Commented Oct 21, 2015 at 11:38
  • date +%Z command in all my servers shows CEST and postgresql is an amazon rds instance with timezone UTC Commented Oct 21, 2015 at 11:44
Related questions
0
Postgres TIMESTAMP query
2
Postgres is ignoring a timestamp index, why?
2
postgresql to_timestamp returns different date than represented by the timestamp
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.