Questions tagged [date]

Questions related to date or date-related functions, data-types, representations (how dates might be stored and displayed) and operations (such as subtracting dates, adding intervals to dates).

Filter by
Sorted by
Tagged with
1
vote
2answers
48 views

Setting a date dynamically

I would like to set a date dynamically in the where clause of my query but I can't figure out how to do it. The query is doing a count of the number of rows with an entered on date >= 1st August ...
0
votes
1answer
11 views

Sharding a MongoDB instance by a spatial key and a date

This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
0
votes
1answer
16 views

Column with Default value as Sum of TIMESTAMP

I have a table which has 2 columns ( JobDate , RecordTime) -------------------------------------- JobDate | RecordTime | SumCol -------------------------------------- 2019-07-20 | 2019-07-...
0
votes
0answers
10 views

How to group by a field in a certain time range in Cassandra

I have a big Cassandra table with around 15 fields, and I wanted to create many materialized views to support all of my queries. The Primary Key on the base table is (CompanyName, ctime) where ctime ...
0
votes
0answers
5 views

Redshift/Athena: Most efficient way to cast timestamps of varying precision to date in query?

I'm receiving a stream of log files to an S3 bucket and querying this data through Amazon Athena (Redshift). Contained in the log files are timestamps with the seconds field having decimals to the ...
-1
votes
0answers
25 views

How to order by strings ASC if it's a calculated field?

I have the following calculated field: CONCAT(idC, '/', DATE_FORMAT(entryDate, '%Y')) as idC I have idC stored on database, that's a ''fake'' id that increments based on another field called ...
3
votes
1answer
26 views

Fill missing dates within groups

I have the following table with values for different stations from 2014-01-01 to 2014-01-04. The data has some date gaps that I want to fill leaving the value as NULL, and assigning the missing date ...
1
vote
1answer
26 views

MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding

In mariadb-10.4.5, running on Windows 10, I created this table (in the local database): `CREATE TABLE IF NOT EXISTS `testbalNoFieldFormat` ( `fig` int(4) NOT NULL, `name` char(10) NOT NULL, `...
-1
votes
1answer
20 views

Why am I not able to filter rows based on date? [closed]

I am trying to apply a filter on dates which is a string type but I am not able to get the desired results. for eg, select * from table_1 where date between '01-01-2016' and '31-12-2016'; The ...
0
votes
1answer
66 views

Unable to use today's date as a criteria in a query

Ok, been a good decade since I've worked with Access and SQL, so a little forgiveness please. Short version - when I add the query criteria to only display rows which match today's date, the query ...
1
vote
1answer
45 views

Convert Date and Filter by Year

I have a date field that is in a 'yyyymmdd' format, and I need to filter the query by the year, using that field. For example, in my table there is a "Posting Date" field that is in the '20180401' ...
0
votes
2answers
91 views

Is it possible to write a SELECT statement to obtain all dates between 2 date columns?

My question is related to these 2 questions: https://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates/27319120 https://stackoverflow.com/questions/1378593/get-a-list-of-...
0
votes
1answer
27 views

How to convert timestamp with time zone to numeric (unix) in PostgreSQL?

I have timestamps with time zone (timestamptz) in my table. Can I convert them to numeric or int in unix timestamp format? I found this question, but it shows only how to convert timestamp without ...
0
votes
0answers
13 views

MySQL date_format German date value not change to date type

I have a column that is varchar and the value of the the data is a date (i.e. 31.05.2019) I'm trying to convert the varchar field to a date field using the following function: date_format(...
0
votes
1answer
49 views

Row for each date, start and end

I have a table that has two columns, startDate and endDate. Each day has its own capacity. Between these dates, user can get from 1 to n items. I am going to get number of items remaining for each day ...
2
votes
3answers
106 views

Count Items not In Time Range

I'm attempting to count the number of items if they are not within 30 seconds of the first item in a "group". I'm having a hard time figuring this out. So, I have this table: WITH ADates AS ( ...
0
votes
2answers
35 views

Date variable to filter result for all previous years

I am querying a sales order table and need to add a variable so that the query always returns a customer list for all customers that have not purchased this year. I don't want to hard code this years'...
2
votes
1answer
64 views

Oracle: Casting timestamp as date and comparing result

I don‘t really understand what cast(timestamp as date) is doing. I am on Oracle Database 11g Express Edition Release 11.2.0.2.0. select cast(to_timestamp('20190516 08:00:00', 'yyyymmdd HH24:MI:SS') ...
0
votes
1answer
32 views

Why does year 0 become year 2000?

select date '0-1-1'; 2000-01-01 Year 0 doesn't exist according to Wikipedia, so shouldn't Postgresql give an error instead of jumping to 2000? Year zero does not exist in the anno Domini system ...
2
votes
2answers
36 views

Filter Table by a Historical 'Last Day of the Month'

I have a daily appending table, that I need a formula for that would filter the table results to just the last day of any given historical month. For example: Table: DwDate Balance ...
1
vote
1answer
74 views

How to get all dates belonging to a timestamp range

I have a tsrange as input. For example: tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) I want to generate all dates belonging to this range. I started with the following, ...
1
vote
1answer
36 views

Subtracting Days From Current Date

I know this is probably very basic, but I'm trying to create a calculated field that subtracts X number of from Current Date, based off of the value of another field: CASE WHEN DaysPD = 30 THEN 1 ...
-1
votes
1answer
44 views

Convert SQL Date MMDDYY varchar to Date?

In reading this, https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017 http://www.sqlusa.com/bestpractices/datetimeconversion/ I cannot locate the ...
1
vote
1answer
26 views

calculate days spent of all visitors from encounters

I have an encounters table CREATE TABLE visitors( id serial PRIMARY KEY, start_date TIMESTAMP NOT NULL, -- visitor_id bigint NOT NULL -- skip for now, end_date TIMESTAMP ); (end_date can be ...
2
votes
1answer
211 views

Splitting a year into pieces

I want to split a given year into given number of date range pieces using SQL. Following points need to be considered while splitting it: Given year will always start from 01-01 and end at 12-31 and ...
1
vote
1answer
37 views

SQL Server 2008 onward - Globalization with Date Time

My application is standalone windows application. Database is SQL Server 2008 onward. The application will be deployed on systems around the globe. User should view the date/time values in their own ...
1
vote
1answer
60 views

Get closest previous date

I need to get the posts closest to a date. SELECT id , datetime FROM dates WHERE id != 150003 AND datetime > FROM_UNIXTIME(1549622615) ORDER BY datetime LIMIT 1 Returns a correct ...
2
votes
2answers
293 views

how to count distinct value per day with start time and end time

my title may seem a bit confusing but here's the context. I have a table 1. It contains the reference code per connection to SSID with value of start time. Now I have a scenario wherein the user ...
-3
votes
1answer
44 views

Can we get any performance gain from creating index from date column?

I have seen lot of indexes created on the date columns in my project. Normally the index is a combination of a date column and another column (like state) which are used to create an index. Since ...
0
votes
1answer
35 views

Get total row by month in multiple tables [closed]

I am new in Oracle database and php, I tried to create a monthly report which covers the whole 12 months of a year. Currently I have 5 different tables which will store data every month for one year. ...
0
votes
2answers
67 views

How can I use mysql to determine the Sundays for the last year?

I have a table where I'm currently aggregating ~200 rows per day of stats. These stats include things like # of users, how many people using X feature, etc. What I'd like to do is query this table ...
1
vote
2answers
305 views

Insert missing dates within table

I have a table with a column for ID, Date and Frequency of occurrence for that particular date and ID. ID date Frequency 1 2014-05-18 5 1 2014-05-19 4 1 2014-05-20 ...
1
vote
1answer
121 views

Select within or closest to date range MSSQL

Lets say we have a table CREATE TABLE [dbo].[Product]( [ProductId] [int] NOT NULL, [Name] [varchar](50) NOT NULL ) CREATE TABLE [ProductPrice]( [ProductPriceId] [int] NOT NULL, [...
0
votes
0answers
8 views

Creating a database for due dates

In my current role I have to write down all my projects on pre-made grid on a dry erase board. As I finish a project I erase it but as the board reaches its capacity I have to manually shift ...
0
votes
1answer
69 views

finding the most recently updated record with multiple date columns

I have recently inherited a SQL Server 2016 database which was converted from an old MS Access db. I have a table that shows various repair dates and test dates for associated serial numbers. I would ...
2
votes
2answers
67 views

Converting and Subtracting Two Dates

I'm trying to get the numeric difference between two dates. (Ex: 11/30/2018 - 11/05/2018 = 25) One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a ...
2
votes
1answer
54 views

Sum values by time

id_ | date_ | hour_ | in_|out_| |…. | …. | …. | …. | …. |19 | 2018-12-12| 18:15:00| 1 | 3 | |19 | 2018-12-12| 18:30:00| 1 | 0 | |19 | 2018-12-12| 18:45:00| 2 | 4 | |...
0
votes
2answers
54 views

Getting Records with Min Max Value on Grouped in Single Table in postgresql

The table looks like this: Existing Table: table_1 Desired Result: table_1 I have been able to get result like the following using this code: table_1 But I would like to get minimum and maximum ...
0
votes
1answer
49 views

how to convert a date string like this MM/YYYY to 01-MM-YY

I have a column and its type is string and stores date something like this 03/1996 I want to convert that to 01-MAR-96? how can I achieve that in ORACLE
0
votes
1answer
513 views

Generate Dates between Date Ranges in mysql

Is there a way to generate dates between date ranges. After looking on SO I found out there is a way to use CTE, another option is to use Union All from 0 to 9. Is there an inbuilt function which I ...
5
votes
3answers
279 views

Getting SQL server to recognise a date column

I am trying to get a count of IDS on a orders table for yesterday (not hardcode) where the date in the table corresponds to when the order was placed. My table looks like this orders ( order_id INT ...
1
vote
1answer
5k views

Select data from the previous 3 months [duplicate]

I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) ) The reason I don't want to use the DATEADD ...
-1
votes
1answer
32 views

MySQL - Classic Date Functions

Version: 10.2.18-MariaDB I'm attempting to find the first available room within December (already included in the sample table) that will be free for 3 consecutive days. The expected output should ...
1
vote
1answer
39 views

Searching through dates query not working?

I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute. I checked my DataBase where date is saved in 1446976737 format which ...
0
votes
1answer
47 views

SAP BO Freehand SQL date formatting (Oracle db)

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query ...
1
vote
2answers
842 views

Fiscal Year dynamic function (Oracle SQL)

My Fiscal Year starts on 1st October (2017) and ends 30th September (2018) I want to write a function that will dynamically calculate first day of October. Currently I have: select Add_Months(Trunc(...
0
votes
1answer
72 views

Fiscal Year to Date filter

This is my data: I need a Fiscal Year to Date logic that will gather all previous months starting October. However, for October 2018 I want to start form 15th, for remaining years it must start from ...
0
votes
1answer
186 views

postgres - node-postgres client does not return rows with between clause for dates

I have a view created as shown below. CREATE OR REPLACE VIEW aggr.v_param_data AS select date(a.received_timestamp) run_date, a.mId, min(a.param_value) min_value, max(a.param_value) max_value, ...
2
votes
1answer
68 views

Filtering previous month data with case statement

These are my available attributes: DT_CURRENT_DT Month Year 11-OCT-18 10 2018 12-OCT-18 10 2018 13-OCT-18 10 2018 14-OCT-18 10 2018 15-OCT-18 10 2018 16-OCT-18 ...
0
votes
1answer
252 views

select Data between two date range mysql return wrong values

I want to select data between 9/Nov/2018 and 11/Nov/2018, but the result seems wrong - it returned data outside the two ranges. Using the below query... select * from radacct where DATE_FORMAT(...