Mysql Date to timestamp SQL Query

Question

I have release date in database as:

release_date
2010-12-02 00:00:00

and i have to get 50 rows from the data order by release date desc. Can anyone tell me how can i implement this in mysql sql query, some thing like

SELECT data.content,item.value
FROM contents 
 AS data 
JOIN items 
 AS item 
WHERE data.id=item.content_id 
ORDER BY data.release_date

please help, any idea will be highly appreciated.

EDIT

I am not asking how to limit it, but my problem is this query makes my script to run forever. Sorry for not clearing this before.

EDIT 2

contents
id | content | Comment | release_date

items
id | content_id | name | release_date

my both tables. Both have around 250000 rows

MySQL is working with date perfectly. It should be OK to sort it with ORDER BY data.release_date DESC without converting it to UNIX Timestamp — Teneff
– Teneff, Commented Oct 20, 2011 at 7:57
yes i tried but this query made my script run forever. its execution doesnt stop. and i also dont see any data as output. — Astha
– Astha, Commented Oct 20, 2011 at 7:58

Shakti Singh · Accepted Answer · 2011-10-20 07:56:35Z

2

Add the DESC and LIMIT in your existing query as like below

SELECT data.content,item.value
FROM contents 
 AS data 
JOIN items 
 AS item 
WHERE data.id=item.content_id 
ORDER BY data.release_date DESC
LIMIT 50

answered Oct 20, 2011 at 7:56

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shakti Singh Over a year ago

@Astha: Do you have indexes created on columns, especially on data.id and item.content_id?

Shakti Singh Over a year ago

@Astha: edit does not have much info, You have to post definition of tables. You can look up for the indexes using the query explain table_nane

JNDPNT · Accepted Answer · 2011-10-20 07:56:41Z

1

same as you have but than:

ORDER BY data.release_date DESC LIMIT 50;

answered Oct 20, 2011 at 7:56

JNDPNT

7,4752 gold badges37 silver badges40 bronze badges

1 Comment

JNDPNT Over a year ago

Could you include a raw database structure?

Ghazanfar Mir · Accepted Answer · 2011-10-20 07:56:46Z

1

Try this

$sql = "select data.content, item.value 
FROM contents as data 
JOIN items as item ON data.id=item.content_id 
ORDER BY data.release_date DESC
LIMIT 50";

Note the DESC for ordering in descending order, remove this clause and it will set to order in ascending order

answered Oct 20, 2011 at 7:56

Ghazanfar Mir

3,5412 gold badges28 silver badges42 bronze badges

Comments

Mark Byers · Accepted Answer · 2011-10-20 07:57:13Z

1

It seems like you are most of the way there already. You missed DESC and you should add a LIMIT clause:

ORDER BY data.release_date DESC
LIMIT 50

answered Oct 20, 2011 at 7:57

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Comments

vicentazo · Accepted Answer · 2011-10-20 08:03:49Z

0

Would be something like:

select data.content,item.value
from contents as data
   join items as item ON data.id=item.content_id
ORDER BY data.release_date

It's better to use ON clausule to join tables.

answered Oct 20, 2011 at 8:03

vicentazo

1,83917 silver badges19 bronze badges

Collectives™ on Stack Overflow

Mysql Date to timestamp SQL Query

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related