im working on a Bash script where I need to subtract 3 hours from user-provided start and end date/time inputs. The user inputs the date and time in the 24-hour format as DD/MM/YYYY HH:MM:SS.
#user will enter 24hours format
Select_Start_date="01/10/2024 12:55:23"
Select_End_date="01/10/2024 16:54:23"
# Convert to the correct format (YYYY-MM-DD HH:MM:SS) before subtracting hours
S_date_adj=$(date -d "$(echo $Select_Start_date | awk -F'/' '{print $3"-"$2"-"$1" "$4}')" -d "3 hours ago" +"%d/%m/%Y %H:%M:%S")
E_date_adj=$(date -d "$(echo $Select_End_date | awk -F'/' '{print $3"-"$2"-"$1" "$4}')" -d "3 hours ago" +"%d/%m/%Y %H:%M:%S")
echo "Actual start date: $Select_Start_date"
echo "Actual End date : $Select_End_date"
echo "Reducted 3 hours start date:$S_date_adj"
echo "Reducted 3 houes End date :$E_date_adj"
# Pipeline Query
SELECT ord.order_num FROM database.s_order ord,
database.S_ORDER_DTL dtl
WHERE ord.created >= TO_DATE('$S_date_adj', 'dd/mm/yyyy hh24:mi:ss')
AND ord.last_upd <= TO_DATE('$E_date_adj', 'dd/mm/yyyy hh24:mi:ss')
I need help ensuring that the date subtraction is correctly 24 hrs formatted and applied in the SQL query.
Result i got:
Actual start date: 01/10/2024 12:55:23
Actual End date : 01/10/2024 16:54:23
Reducted 3 hours start date:03/10/2024 08:21:49 ( Suppose 12 -3 =9:55:23)
Reducted 3 houes End date :03/10/2024 08:21:49 ( suppose 16-3 =14:54:23)
$ Select_Start_date="01/10/2024 12:55:23"
$ date -d "$Select_Start_date"
Wed Jan 10 12:55:23 EST 2024
$ date -d "- 3 hours $Select_Start_date"
Wed Jan 10 09:55:23 EST 2024
$ date -d "$Select_Start_date - 3 hours"
Wed Jan 10 11:55:23 EST 2024
Putting the delta time first works OK
When you put -3 after the datetime, the date parser interprets that as the timezone offset.
All the gory details at Date input formats