find time half hour before "06:00 AM" in sql

Question

I have a column of times in my table. I believe they are now in strings format.

The format of each time is just represented in hours, for example "06:00 AM".

How can I get a time half hour earlier and still in the same format in another column, i.e., "05:30 AM"?

I only want to use SparkSQL queries to solve this, as I'm working in a Databricks notebook.

Thanks in advance!

I have tried something like

SELECT DATEADD(MINUTE, -30, '06:00 AM') AS new_time;

which returned null.

Answer 1

This worked for me

SELECT date_format(new_time, 'hh:mm a') AS formatted_time
FROM (
    SELECT DATEADD(MINUTE, -30, '06:00') AS new_time
)