Solved

MYSQL: Extract Date from Timestamp Field

Posted on 2008-10-02
1
1,532 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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