data conversion

[artistlikeu]

i have time column in my database whose data type is "time" i.e 00:00:00.
in python i make a query and get this table as string.
I want this time only in seconds i.e. integers.
What i have to do???

[michaelb]

First of all you need to decide where you want this conversion to take place.
It can be done on the database, when you run the query, or it can be done by the client (your Python script, I suppose) when you already have the results.

Questions to ask are typically these:
- would you ever need this value in its original, unchanged form?
- how likely it is that you will migrate your app from Postgres to another db?
- how likely it is that you will replace your client code (e.g. change Python to TCL, or C API) ?

At some point performance may come into picture as well. If your query retrieves large number of rows you may want to run a test to see if manipulating data on the database will give you a significant gain over massaging values in your client environment.

As for the tools suited to do this job I believe that either db or your script won't have a problem with this conversioin.

[artistlikeu]

Dear michael,

i will not change language... i will only use python.
i will only use postgreSQL... no other database..

now tell me how this conversion can be taken place in

1) database level OR
2) python coding

Regards
Artist

First of all you need to decide where you want this conversion to take place.
It can be done on the database, when you run the query, or it can be done by the client (your Python script, I suppose) when you already have the results.

Questions to ask are typically these:
- would you ever need this value in its original, unchanged form?
- how likely it is that you will migrate your app from Postgres to another db?
- how likely it is that you will replace your client code (e.g. change Python to TCL, or C API) ?

At some point performance may come into picture as well. If your query retrieves large number of rows you may want to run a test to see if manipulating data on the database will give you a significant gain over massaging values in your client environment.

As for the tools suited to do this job I believe that either db or your script won't have a problem with this conversioin.

[michaelb]

In Python look at using regexp, or perhaps even better regsub - just replace the ":" separators with an empty string in the time value.
Maybe there's something better available there, but I don't know Python.

In Postgres there is a variety of parsing/formatting functions available to you
Suppose you have table tab1 with field ftime

-- get the raw value
select ftime from tab1 ; => 12:20:45

-- remove all occurences of the colon character:
-- (note usage of single quotes in all expressions below)
select replace(ftime, ':', '') from tab1 ; => 122045

select to_char(ftime, 'HH24MISS') from tab1 ; => 122045

select to_number (ftime, '09G99G99') from tab1 ; => 122045
(here watch whether this call would preserve the leading zero, if any)


Pick what you like best, I'd favor usage of to_char function.
You may find it helpful to browse the Postgres manual.

[artistlikeu]

Thank you but here is a problem.
This only converts time into numbers. It did not translate time into seconds.
for example in yr example 12:20:45
result must be 44460 (seconds).

Can u elaborate it please.
Regards

In Python look at using regexp, or perhaps even better regsub - just replace the ":" separators with an empty string in the time value.
Maybe there's something better available there, but I don't know Python.

In Postgres there is a variety of parsing/formatting functions available to you
Suppose you have table tab1 with field ftime

-- get the raw value
select ftime from tab1 ; => 12:20:45

-- remove all occurences of the colon character:
-- (note usage of single quotes in all expressions below)
select replace(ftime, ':', '') from tab1 ; => 122045

select to_char(ftime, 'HH24MISS') from tab1 ; => 122045

select to_number (ftime, '09G99G99') from tab1 ; => 122045
(here watch whether this call would preserve the leading zero, if any)


Pick what you like best, I'd favor usage of to_char function.
You may find it helpful to browse the Postgres manual.

[michaelb]

Oh, sorry, I misunderstood what you were looking for.
(should've had more coffee before reading or posting here...)

Try this:

select to_char(ftime, 'SSSS') from tab1 ;