Select specific columns, along with the rest

Question

I have a table with a lot of columns. Some of these are DATETIME, which I turn into Unix timestamps with UNIX_TIMESTAMP(). So I don't have to type out all the other columns I want from the table, is there a way of doing something like:

SELECT UNIX_TIMESTAMP(t.start) AS start,
       UNIX_TIMESTAMP(t.end) AS end,
       t.theOtherColumns
FROM table t

Where t.theOtherColumns is the rest of the columns in the table. To explain further; I want to select all the columns from the table, perform operations on some of them, but not type out each column name into the query.

When I do, say,

SELECT UNIX_TIMESTAMP(t.start) AS start,
       UNIX_TIMESTAMP(t.end) AS end,
       t.theOtherColumns
FROM table t

It selects start and end twice. I only want to return the start and end columns from UNIX_TIMESTAMP(), and exclude those columns from the t.* set.

@Zoltan - But that will bring back `t,start` and `t.end` also. The answer to this question is "No" BTW — Martin Smith, Sep 15 '11 at 13:37
@Jam - You can of course create a `View` if this is a repetitive need though. — Martin Smith, Sep 15 '11 at 13:42
Or you can just remove those extra values server side with whatever programming language you are using to fetch the data. — Brian Glaz, Sep 15 '11 at 13:51
Though just to be clear I am not recommending the use of `*`. The function of the `view` would be to avoid keep having to write `UNIX_TIMESTAMP(t.start) AS start, UNIX_TIMESTAMP(t.end) AS end ...` — Martin Smith, Sep 15 '11 at 14:03
Thanks for all your comments. I'll just stick to typing each one out - it's only for one, possibly two queries anyway. — Bojangles, Sep 15 '11 at 15:40
@JamWaffles take a look at [my answer below](http://stackoverflow.com/a/13880100/1639910) as it should give the results this question was looking for. If you think it is a more accurate answer I would suggest you marking it as the answer so that other users can more readily find it. I know it has been a while since you have asked this question but I hope that my answer can help you and others. — donL, Dec 17 '12 at 12:37

score 5 · Answer 1 · edited May 23 '17 at 12:26

What you can do is use this answer to help build the results you want.

A possible solution would look like

SET @sql = CONCAT('SELECT UNIX_TIMESTAMP(t.start) AS start, UNIX_TIMESTAMP(t.end) as end,', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'test' AND table_name = 
't' AND column_name NOT IN ('start', 'end')), 
' from test.t');  
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

*Replace test with the name of the schema that contains your table t.

score 4 · Answer 2 · answered Sep 15 '11 at 13:37

4

Try t.* it works under Oracle.

answered Sep 15 '11 at 13:37

HamoriZ

2,370
18
38

score 1 · Accepted Answer · answered Sep 15 '11 at 13:37

1

I don't believe there is a way to do this as you suggested, but you can do this

SELECT t.*, UNIX_TIMESTAMP(t.start) AS start, UNIX_TIMESTAMP(t.end) as end ...

answered Sep 15 '11 at 13:37

Brian Glaz

15,468
4
37
55

There is a way this can be done. [See my answer below.](http://stackoverflow.com/a/13880100/1639910) – donL Dec 14 '12 at 17:35

Select specific columns, along with the rest

3 Answers3