MYSQL selecting date on timestamp

Question

I know this question have many similar posts but I'm having a hard time finding what I truly need.

I want to get only date from timestamp which I got the answer here. I tried that using the query below:

SELECT *, DATE('2014-04-26') FROM labor

Code above is working perfectly fine. But I want to know how do I get all records including there timestamp but only extracting the date from the timestamp

If I'm using a wrong datatype please let me know. My table structure is like this:

enter image description here

Thank you in advance.

Why did I get I minus on my post? – newbie Apr 26 '14 at 13:11 — newbie, Apr 26 '14 at 13:11

Sadikhasan · Accepted Answer · 2014-04-26T13:20:59.477

2

Try this

SELECT *, DATE(last_update) FROM labor;

edited Apr 26 '14 at 13:20

answered Apr 26 '14 at 13:05

Sadikhasan

18,365
21
80
122

`SELECT *, DATE('last_update') test FROM labor` The `test` column from the select is null. – newbie Apr 26 '14 at 13:09

score 2 · Answer 2 · edited May 23 '17 at 12:11

2

You'll need

SELECT *, DATE(FROM_UNIXTIME(last_update) FROM labor

See also this question detailing timestamp conversions.

edited May 23 '17 at 12:11

Community

1
1

answered Apr 26 '14 at 13:07

Tomas Pastircak

2,867
16
28

What is the difference of this from `SELECT *, DATE('last_update') FROM labor;`? – newbie Apr 26 '14 at 13:12
@newbie appears that there is no real difference - I was not aware that it is possible to call DATE with giving a timestamp. The other answers are definitely better. – Tomas Pastircak Apr 26 '14 at 13:15

score 1 · Answer 3 · answered Apr 26 '14 at 13:06

1

You can do as

select 
    col1,
    col2,
    ...
    coln,
    date(last_update)
    from labor

col1. col2..coln are the column names from your table except the last_update.

answered Apr 26 '14 at 13:06

Abhik Chakraborty

44,654
6
52
63

MYSQL selecting date on timestamp

3 Answers3