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
20130311152500   20130311152538
20130311152539   20130311152614
20130311152740   20130311152806
  • 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. Mar 12 '13 at 21:55
  • @shabu - did you get the info you wanted? Mar 15 '13 at 7:33

Something like this?

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

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

  • 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. Mar 12 '13 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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