I have a date value as 2/24/2015 16:30:00-05:00 store in the DB column,
I need to substract the -05:00 from the actual date 2/24/2015 16:30:00.
How do i use the extract function to do this,
Simply TS_column AT '00:00' will shift the time zone.
But if you insist on using EXTRACT, you can compute the time zone offset as an interval this way:
EXTRACT(TIMEZONE_HOUR FROM TS_column) * interval '1' hour + EXTRACT(TIMEZONE_MINUTE FROM TS_COLUMN) * interval '1' minute
By the way: no approval necessary :)
this gives me only the time zone , in this case -5:00,
I need to subtract the -5 from the date,
so for 2/24/2015 16:30:00-05:00
it should return 2/24/201511:30:00
if it is 2/24/2015 00:30:00-05:00
it should return 2/23/2015 19:30:00
The obvious response would be: So, do that!
Note that subtracting -5 means adding 5, though, so 02/24/2015 16:30:00 US Eastern is 02/24/2015 21:30:00 UTC.
But what is the real requirement? Do you just want to display UTC equivalent time but omit the time zone specifier from the display?
CAST(CAST(TS_column AT '00:00' AS FORMAT 'mm/dd/yyyyBhh:mi:ss') AS CHAR(19))
If you insist on converting to a "timestamp without explicit time zone" (i.e. implicitly session local time zone) first and then back out the extracted offset, you may have to deal with daylight saving time (summer time) shifts.