CODEWITHSUNDEEP
mysqlsqloracle-database
Ray J Tong
Ray J Tong

Reputation: 897

Oracle Date Aggregating

I have a table in Oracle that has the daily sum of values.

My goal is to have this table display the monthly sum of values instead of daily.

I have this completed by using this query:

select to_char(dateid, 'YYYY-MM') as thedate, count(*)
from picture_table
group by to_char(dateid, 'YYYY-MM')
order by 1, 2

The issue here is that I have to transfer this oracle table to a table in MySQL. Since MySQL's DATE data structure is forced to be in the format 'YYYY-MM-DD', I am thinking I must append all my months to be 'YYYY-MM-01' so that "01" stands for the entire sum of the month.

Is it possible to do an oracle query to append a -01 to all my dates so they can be pipelined to MySQL table correctly?

Upvotes: 1

Views: 141

Answers (3)

Justin Cave
Justin Cave

Reputation: 231651

It would generally make more sense (and be more efficient) to use the TRUNC function to truncate the date to the first of the month. The resulting value will be of type DATE.

SELECT trunc(dateid, 'MM' ) thedate, count(*)
  FROM picture_table
 GROUP BY trunc(dateid, 'MM')
 ORDER BY 1, 2

It is not obvious what you mean by "pipeliend to MySQL" in your question. I assume that whatever you are using to move data from Oracle to MySQL will be able to correctly handle mapping an Oracle DATE to a MySQL DATE or DATETIME. It would seem very unusual if you had to convert the date in Oracle to a string in a particular format just to have MySQL perform an implicit conversion of that string back to a DATE.

Upvotes: 2

user330315
user330315

Reputation:

You can format the date just the same in MySQL, you only need a different function:

select date_format(dateid, '%Y-%m') as thedate, count(*)
from picture_table
group by date_format(dateid, '%Y-%m')
order by 1, 2

Example is here: http://sqlfiddle.com/#!2/af6bd/2

Description of the date_format() function: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

You would do that with a simple concat:

select to_char(dateid, 'YYYY-MM')||'-01' as thedate, count(*)
from picture_table
group by to_char(dateid, 'YYYY-MM')
order by 1, 2

However, why is the mysql table using a date field? It could just have a varchar() column, to accept the "yyyy-mm" format?

Upvotes: 1

Related Questions