Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MYSQL: Extract Date from Timestamp Field

Posted on 2008-10-02
1
Medium Priority
?
1,542 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
1 Comment
 
LVL 1

Accepted Solution

by:
fReAkCoRp earned 500 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

577 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