Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2014
    Posts
    21

    Unanswered: Create table - time without seconds

    Hello,

    I want my time data in table to be entered without seconds. How can I do that ?

    My current SQL syntax is :

    CREATE TABLE MyTable (
    Start_Time TIME)

    This stores time in table as "16:00:00" by default. As I said, I want only hours and minutes.

    Thanks for help !

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    By controlling the values you insert into the db
    Access stores times as the proportion of a day. So if you want only the hour and minute components then don't set a second.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2014
    Posts
    21
    Hi healdem,

    I'm newbie in DB2, so I'm not sure what you mean exactly. I have .csv file where there are only hours and minutes, but when I'm importing in DB2, seconds are added. So how can I create table only with hours and minutes - SQL syntax please ?

    Or do I have to add syntax in db2cmd command, like "db2 import from Mycsv.file of del *modified by time="HH:MM" insert into Mytable" ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ooops sorry matey, got confused, thought this was pisted in the access forum.... im sure there will be a DB2 answer sometime soon
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    heraldem might be onto something though.. Excel could be storing the time values in an internal format that retains the seconds, you just don't see them.
    If you open the .csv file in NOTEPAD, what do you see for the time values?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I would strongly recommend using the standard date time datatypes for the table design. Enforce the 'no seconds' rule through a constraint and/or data cleaning. If the data is coming from a csv then for now id tidy it up there (reformat it)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2014
    Posts
    21
    Thanks both of you for replies.
    Times in .csv or notepad are same, in format without seconds.
    Seconds are added only in DB2 when I use import command to table. Table is created in TIME, so that's why seconds.

    So healdem, you suggest that I include seconds in .csv too, for not having problems with DB2 ?

  8. #8
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    TIME data type stores seconds in DB2. Import utility inserts times in HH:MM correctly from del file and assigns 00 to seconds.
    If you want not to store seconds, you have an option to store such times in a string format, but it's not convenient.
    Can you describe why you need not to store seconds in DB2? Are there some real business needs compelling you to do this?
    Isn't it easier just not to show seconds in your user application?
    Regards,
    Mark.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Sorry im confused here (and not for the first time ).
    Is your problem that errant seconds are being stored in tbe column or is this a presentational problem, ir you just need to hide seconds when displaying the valye of the column.


    I dont use DB2, well havent for nearly 25 years, but it should be possible to put some form of constraint that enforces no seconds to be stored. Alternatively consider using a stored procedure to strip off seconds (well set to 0) whenever you insert a row or modify the temporal columns.

    There will be a format function that allows you to show whatever parts of datetime datatype you wish.

    Failing that if you must store this type of data in a non datetime column the least worse solution in my books would be as an integer number of minutes and display using a format function or sp
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    time is a standard format
    when selecting the column just select rtrim(char(hour(colname)) concat ':' concat char(minute(colname)) from
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Oct 2014
    Posts
    21
    I think you ali got me answers I needed.

    I'm not DBA, I'm just using DB2 for learning purposes. All I wanted to know is If It's possible to construct a table without seconds, as It was strange that in my .csv or Excel file there are no seconds. But looks like It doesn't matter since I can remove seconds in user application - so thanks przytula_guy for that, your answer is enough for me

    To answer all others - I didn't know that DB2 or any other DBMS stores time in this format by default, that's why I posted question.

  12. #12
    Join Date
    Aug 2003
    Location
    austin,tx
    Posts
    104
    If your input does not have the seconds stored, I would not expect DB2 would add them on the import.
    So if I saw that, like you I would be trying to figure out why because it would make me think I am overlooking something or making a mistake somewhere. That's why I was curious what you saw in the csv file opened in a text editor (where you would likely see the raw values).

    The most common strategy in most "computer systems" is to store date/time values internally as an integer which is the number of seconds/milliseconds since some set starting date/time. I am not certain how db2 or excel store time data internally, but I would guess it's something similar.

    Anyway, looks like your problem is solved, but if you wanted to clean them up in the db, something like this would zero out the seconds.
    db2 "UPDATE myTable SET MyTimeCol = MyTimeCol - SECOND(MyTimeCol) SECONDS"

  13. #13
    Join Date
    Oct 2014
    Posts
    21
    Ok, thanks for that.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •