Solved

MYSQL: Extract Date from Timestamp Field

Posted on 2008-10-02
1
1,531 Views
Last Modified: 2012-06-27
I have a table that has only two fields: dtmDateTime timestamp field; fData float field.  I want to group data by max, min, avg by date.  How can I do this via SQL SELECT.  I tried select convert(varchar,dtmdatetime,104) from tablename; but it does not work - error is returned for convert.  Any help extracting the date from the timestamp field would be appreciated
0
Comment
Question by:DCCoolBreeze
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
fReAkCoRp earned 125 total points
ID: 22629836
To select the Date from the filed you can use the Date Function you can used like this example:
SELECT date(dtmDateTime) as DateField,fData as DataField FROM test t;
for the max , min and avg you can go from this examples:
Group by :
SELECT date(dtmDateTime) as DateField,fData as DataField, count(*) as CountField FROM test t group by DateField
MAX and MIN
SELECT min(date(dtmDateTime)) as DateField, fData as DataField, count(*) as CountField FROM test t
group by DataField
for and avg you can go like this
SELECT sum(fData)/count(*) as from FROM test t where date(dtmDateTime)=date('2001-01-01');
you can use the sum function and add all the fData and divided by the count of data that matches the date you want to have your query based on.
this are just some examples but if you can post more info in what are you going to do maybe I can help you more.
Hope this works.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Creating and Managing Databases with phpMyAdmin in cPanel.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question