How do I convert string to time in postgresql?

Question

I have a column of type varchar with a time on it like 120217. I would like to convert this to a time format I can query. Would something like

alter table public.table alter column col_time type date using to_date(col_time, 'HHMMSS');

work?

does this work as meant alter table public.table alter column col_time type date using (col_time)::time; ?.. — Vao Tsun
– Vao Tsun, Commented Feb 7, 2017 at 16:34

Dervin Thunk · Accepted Answer · 2017-02-07 21:34:39Z

2

this should do:

alter table public.table 
alter column col_time 
type time using (col_time::time);

edited Feb 7, 2017 at 21:34

Dervin Thunk

20.2k31 gold badges133 silver badges229 bronze badges

answered Feb 7, 2017 at 16:35

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Dervin Thunk Over a year ago

Thanks! May I ask what's wrong with the solution I posted, just to learn of the difference?

Vao Tsun Over a year ago

to tell that I have to see error or output of your statement. I just decided to use implicit cast. to_date returns date - I suppose it just ignores your HHMMSS mask: postgresql.org/docs/current/static/functions-formatting.html

Dervin Thunk Over a year ago

your solution gives me

ERROR:  result of USING clause for column "col_time" cannot be cast automatically to type date HINT:  You might need to add an explicit cast.

Vao Tsun Over a year ago

you probaby have int in col_time then. but then you should have error when trying to run to_date(int, text)... anyway - updated answer with explicit cast

Vao Tsun Over a year ago

@DervinThunk - obviously no implicit conversion date to time. Missed it because did not mock up column. Thank you for edit!

Collectives™ on Stack Overflow

How do I convert string to time in postgresql?

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related