Example
Return a specified part of a date:
SELECT DATENAME(year, '2017/08/25') AS DatePartString;
Try it Yourself »Definition and Usage
The DATENAME() function returns a specified part of a date.
This function returns the result as a string value.
Syntax
Parameter Values
ParameterDescriptionintervalRequired. The part to return. Can be one of the following values:- year, yyyy, yy = Year
- quarter, qq, q = Quarter
- month, mm, m = month
- dayofyear = Day of the year
- day, dy, y = Day
- week, ww, wk = Week
- weekday, dw, w = Weekday
- hour, hh = hour
- minute, mi, n = Minute
- second, ss, s = Second
- millisecond, ms = Millisecond
Technical Details
Works in:SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data WarehouseMore Examples
Example
Return a specified part of a date:
SELECT DATENAME(yy, '2017/08/25') AS DatePartString;
Example
Return a specified part of a date:
SELECT DATENAME(month, '2017/08/25') AS DatePartString;
Try it Yourself »Example
Return a specified part of a date:
SELECT DATENAME(hour, '2017/08/25 08:36') AS DatePartString;
Try it Yourself »Example
Return a specified part of a date:
SELECT DATENAME(minute, '2017/08/25 08:36') AS DatePartString;
Try it Yourself »If I understand correctly, your query returns one record for a specific key. However, this isn’t what I need.
I don’t want to specify the key because the query should return all documents with all keys. Maybe I wasn’t clear enough, so I’ll share an example.
Here is a document in my collection in mongodb 4.4.12(could not use latest 5.0.6 because of the Intel AVX support required by mongodb 5.0.0 and above
I really want to use the native mongodb time series but for now i can’t
Now i got the context out of the way
Now back to the document, here it is
{ _id: ObjectId('61fbeb4e41691f4d9f012434'), time_stamp: ISODate('2022-02-03T14:48:11.000Z'), trading_pair: '1INCH-BTC', price: 0.0000439, status: 'online', trading_disabled: false }and these documents get inserted every 1 minute, sometimes a minute is skipped and 2 minute range before next insert, even though i run the cronjob every 1 minute, but many factors that does not allow every minute always
{ _id: ObjectId('61fbeb4e41691f4d9f012432'), time_stamp: ISODate('2022-02-03T14:45:11.000Z'), trading_pair: '1INCH-BTC', price: 0.0000437, status: 'online', trading_disabled: false }, { _id: ObjectId('61fbeb4e41691f4d9f012433'), time_stamp: ISODate('2022-02-03T14:47:11.000Z'), trading_pair: '1INCH-BTC', price: 0.0000438, status: 'online', trading_disabled: false }, { _id: ObjectId('61fbeb4e41691f4d9f012434'), time_stamp: ISODate('2022-02-03T14:48:11.000Z'), trading_pair: '1INCH-BTC', price: 0.0000439, status: 'online', trading_disabled: false },Please note i insert many documents all at once using same exact ISODate, so i can have like 100 documents all using same ISODate with different and unique values for trading_pair…which is why i want to query by date.
Eventually i will move over to using the native timeseries MongoDB's New Time Series Collections
What i want to do is be able to
I want to be able to return only the most recent single document when i search. So it will return all documents from the most recent InsertMany …so i can always get the most recent price data for all trading_pair
How do i perform percentage change to calculate percentage price difference for a trading_pair between different ISODate for like last 5 minutes, 24 hours, 7 days? This part is the one i really want to see how to do. I am new to MongoDB , in the sense i haven’t used in PRODUCTION app before but now it is that time to fo it, and i want to do it right(need your help here)