Substract an hour from date SQL

Question

I have an entity with a date property represented as a String (I can't change it to Date or Timestamp).I need to get all newest records (not older than an hour). I have a SQL query that returns the last (youngest) record, now I need to substract an hour from it somehow:

SELECT p FROM Invoice p WHERE date >= ((SELECT MAX(date) FROM Invoice)

it's Java and Posgresql. Can this be done using SQL when date is represented as a String?

possible duplicate of [PostgreSQL date difference](http://stackoverflow.com/questions/14071630/postgresql-date-difference) — gustavohenke, Mar 11 '15 at 12:31
You need to [parse it](http://stackoverflow.com/questions/3296725/parse-date-in-mysql) then use [`DATE_SUB`](http://stackoverflow.com/questions/6020162/how-to-subtract-3-hours-from-a-datetime-using-sql). Without storing the data as a date of some sort, this will be **horribly** slow. — Boris the Spider, Mar 11 '15 at 12:34

score 2 · Answer 1 · edited Mar 11 '15 at 19:35

2

SELECT p, to_timestamp(p.data, 'YYYYMMDD HH12MI') - interval '1 hour' new_dt
FROM Invoice p WHERE date >= ((SELECT MAX(date) FROM Invoice)

http://www.postgresql.org/docs/current/static/functions-datetime.html

http://www.postgresql.org/docs/current/static/functions-formatting.html

You will have to modify your date format pattern for your to_timestamp. I don't know what format your DB has it in.

edited Mar 11 '15 at 19:35

Erwin Brandstetter

605,456
145
1,078
1,228

answered Mar 11 '15 at 12:44

Paul Zepernick

1,452
1
11
25

ERROR: conflicting values for "mm" field in formatting string DETAIL: This value contradicts a previous setting for the same field type. DB example: 2015/03/11 12:55:55 – jarosik Mar 11 '15 at 12:52
Did you update 'YYYYMMDD HH12MI' to the same format that you have in your DB? – Paul Zepernick Mar 11 '15 at 12:54
Yes I did: 'yyyy/MM/dd HH:mm:ss; – jarosik Mar 11 '15 at 12:55
Postgre has its own date format. SimpleDateFormat does not work in there. Check our the functions-formatting.html link. Try this out: YYYY/MM/DD HH:MI:SS – Paul Zepernick Mar 11 '15 at 13:13

score 0 · Answer 2 · answered Mar 11 '15 at 13:30

0

In java its possible by parsing.but it is not possible in sql,because the datatype you are getting the date is String.

answered Mar 11 '15 at 13:30

thrinod

13
2

would String comparison in SQL actually work? SELECT p FROM Invoice p WHERE date >= :lastHour assuming lastHour is properly parsed String (with one hour substracted) – jarosik Mar 11 '15 at 17:34

score 0 · Answer 3 · answered Mar 11 '15 at 13:51

0

assuming date is a column name,

select * from Invoice where "date"::date > now()-'1 hour'::interval

answered Mar 11 '15 at 13:51

Vao Tsun

47,234
13
100
132

Erwin Brandstetter · Answer 4 · 2015-03-12T03:08:20.930

Use to_date() to convert the string to an actual date.
While being at it: your query to get the latest row can be improved, too:

SELECT *
     , to_date(date, 'YYYY-MM-DD') - interval '1 hour' AS ts
FROM   Invoice p
ORDER  BY date DESC NULLS LAST
LIMIT  1;

Assuming ISO format. Either way, your text representation has to work for the sort order, or you need to do more.

Subtracting an interval from a date results in a timestamp automatically.

Don't select p, that's the table alias and would get you the whole row as single column instead of individual columns.

ORDER BY / LIMIT 1 is faster and more reliable for the purpose. Your construct could return multiple rows.

You should have an index on date to make this fast.

You should still store this as date, not as string. Even if you say you can't.

And don't use basic type names as column names.

Substract an hour from date SQL

4 Answers4