Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am new to PostgreSQL. I am trying to write a query which can give me duration of the time. The fields are in the format yyyymmddhhmmss. In between we will get empty fields for Start_Time or End_Time. I want to skip these rows and get the output.

Start_Time          End_Time 
20130312080535   20130312080550
20130312080018   20130312080028
20130312080030   20130312080049
20130311154049   20130311154138
20130311225510  
20130311152500   20130311152538
20130311225510  
20130311152539   20130311152614
20130311152740   20130311152806
share|improve this question
    
What is "duration of the time" supposed to mean? If you are not sure of the language you can always supply a sample output to clarify what you are after. Also, you need to supply the data types of the columns and always the version of PostgreSQL - even if it shouldn't matter much in this case. –  Erwin Brandstetter Mar 12 '13 at 21:55
    
@shabu - did you get the info you wanted? –  trygvis Mar 15 '13 at 7:33

1 Answer 1

Something like this?

SELECT
  to_timestamp(end_time, 'YYYYMMDDHH24MISS') -
  to_timestamp(start_time, 'YYYYMMDDHH24MISS') as "time_interval"
FROM
  measurement
WHERE
  end_time IS NOT NULL;

There's an sqlfiddle if you want to adjust your question: http://sqlfiddle.com/#!1/576d4/6

share|improve this answer
    
What is measurement in here? –  shabu 224 Mar 12 '13 at 9:02
    
It's explained in the link. It is just a test table I made to test and explain the query. –  trygvis Mar 12 '13 at 9:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.