Is it possible to group by day, month or year with timestamp values?

Question

I have a table ORDERS(idOrder, idProduct, Qty, OrderDate) where OrderDate is a varchar column with timestamp values, is it possible to get the Qty of each day, week, month or year ?

The table looks like this :

---------------------------------------
|idOrder | idProduct | Qty | OrderDate|
---------------------------------------
| 1      |  5        | 20  | 1504011790 |
| 2      |  5        | 50  | 1504015790 |
| 3      |  5        | 60  | 1504611790 |
| 4      |  5        | 90  | 1504911790 | 
-----------------------------------------

and i want something like this

------------------------------
| idProduct | Qty | OrderDate|
-------------------------------
|  5        | 70  | 08/29/2017|
|  5        | 60  | 09/05/2017|
|  5        | 90  | 09/08/2017| 
-------------------------------

Tag your question with the database you are using and provide sample data and desired results. — Gordon Linoff, Sep 08 '17 at 22:58

Mr. Em · Accepted Answer · 2017-09-11T20:55:53.533

looks like you want to do 2 things here: first group by your idProduct and OrderDate

select idProduct, sum(Qty), OrderDate from [yourtable] group by idProduct, OrderDate

This will get you the sums that you want. Next, you want to convert time formats. I assume that your stamps are in Epoch time (number of seconds from Jan 1, 1970) so converting them takes the form: dateadd(s,[your time field],'19700101')

It also looks like you wanted your dates formatted as mm/dd/yyyy. convert(NVARCHAR, [date],101) is the format for accomplishing that

Together:

select idProduct, sum(Qty), convert(NVARCHAR,dateadd(s,OrderDate,'19700101'), 101) from [yourtable] group by idProduct, OrderDate

O_O ! THANK YOU SOO MUCH – Angela Petit Sep 09 '17 at 15:56 — Angela Petit, Sep 09 '17 at 15:56

score 1 · Answer 2 · answered Sep 08 '17 at 23:36

Unfortunately, the TSQL TIMESTAMP data type isn't really a date. According to this SO question they're even changing the name because it's such a misnomer. You're much better off creating a DATETIME field with a DEFAULT = GETDATE() to keep an accurate record of when a line was created.

That being said, the most performant way I've seen to track dates down to the day/week/month/quarter/etc. is to use a date dimension table that just lists every date and has fields like WeekOfMonth and DayOfYearand. Once you join your new DateCreated field to it you can get all sorts of information about that date. You can google scripts that will create a date dimension table for you.

Thank you so much, i was going for this solution until @MattDekinder gave me exactly what i was looking for ^^" — Angela Petit, Sep 09 '17 at 15:58

score 0 · Answer 3 · answered Sep 08 '17 at 23:01

0

Yes its very simple:

TRUNC ( date [, format ] )

Format can be:

TRUNC(TO_DATE('22-AUG-03'), 'YEAR')
Result: '01-JAN-03'

TRUNC(TO_DATE('22-AUG-03'), 'MONTH')
Result: '01-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DDD')
Result: '22-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DAY')
Result: '17-AUG-03'

answered Sep 08 '17 at 23:01

Ted at ORCL.Pro

1,602
1
7
10

i am sorry i guess i mislead you when i didnt give a glimpse at how my table look like – Angela Petit Sep 08 '17 at 23:11
Still TO_DATE and TRUNC is what you need ;) – Ted at ORCL.Pro Sep 08 '17 at 23:12
I am working with sql server, and TO_DATE is Oracle function i believe ^^" – Angela Petit Sep 08 '17 at 23:18
Oh I see! In tha case try out -- Convert the current date to YYYY-MM-DD format SELECT CONVERT(VARCHAR(10), GETDATE(), 120); – Ted at ORCL.Pro Sep 08 '17 at 23:23

Is it possible to group by day, month or year with timestamp values?

3 Answers3