By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 1,552 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

data conversion

P: 38
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???
Nov 6 '06 #1
Share this Question
Share on Google+
5 Replies


Expert 100+
P: 534
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.
Nov 6 '06 #2

P: 38
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.
Nov 6 '06 #3

Expert 100+
P: 534
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.
Nov 6 '06 #4

P: 38
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.
Nov 6 '06 #5

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

Try this:
Expand|Select|Wrap|Line Numbers
  1. select to_char(ftime, 'SSSS') from tab1 ;
Nov 6 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.