Extract TIME from DATETIME - informix

sql timestamp informix etl
fenix picture fenix · Aug 1, 2014 · Viewed 14.8k times · Source

I have lost few hours looking for one row solution and didn't manage to find it. I have some value (2013/01/03 07:13:26.000) and I want to extract time part to get this: 07:13:26 so I can store it in another database as stage layer in ETL process.

When I try select my_datetime::datetime hour to second, I still get full timestamp.

Thanks.

EDIT: Sorry, source column is datetime, NOT timestamp.

Answer

fenix picture fenix · Aug 1, 2014

Ok, I managed to do this:

SELECT to_char(extend (my_datetime_column, hour to second),'%H:%M:%S') as my_time FROM my_table

Hope it will help someone!

Related questions

Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL

I want to extract just the date part from a timestamp in PostgreSQL. I need it to be a postgresql DATE type so I can insert it into another table that expects a DATE value. For example, if I have 2011/05/26 09:00:00, …

Read More
How to select rows that have current day's timestamp?

I am trying to select only today's records from a database table. Currently I use SELECT * FROM `table` WHERE (`timestamp` > DATE_SUB(now(), INTERVAL 1 DAY)); But this takes results for the last 24 hours, and I need it to only …

Read More
Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine

I have a sqlite (v3) table with this column definition: "timestamp" DATETIME DEFAULT CURRENT_TIMESTAMP The server that this database lives on is in the CST time zone. When I insert into my table without including the timestamp column, sqlite …

Read More