Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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).

2
votes
1answer
25 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
29 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
33 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
65 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
25 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
38 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 ...
0
votes
0answers
28 views

Creating a relationship for a database model based on that model's date range

I have a database of Schedule objects in PSQL (schedule_id, user_id, date_range ( [date, date) ), and a few other fields not necessary for this discussion). Each of these Schedule objects is ...
1
vote
1answer
24 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
118 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
29 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
35 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
247 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
43 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
34 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
64 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
167 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
91 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
45 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
48 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
51 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
46 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
31 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
378 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
273 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
3k 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
30 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
38 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
44 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
507 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
70 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
107 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
210 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(...
1
vote
3answers
140 views

PL/SQL date or to_char question

begin if TO_CHAR('23-OCT-1994','DAY') = 'FRIDAY' THEN DBMS_OUTPUT.PUT_LINE('FRIDAY'); else DBMS_OUTPUT.PUT_LINE('Not Friday'); end if; end; / I am trying to ...
0
votes
1answer
50 views

How do I generate a date series in PostgreSQL without hard coding the dates? [duplicate]

I want to generate a series of months. The start should be the first month of the current year, the stop is the current date with an interval of 1 month. I used the function generate_series (see ...
2
votes
1answer
143 views

SET DATESTYLE TO 'European'; in MySQL

I'm doing MySQL exercises and I found some of PostgreSQL, and there is a part where before entering the data to the newly created tables, enter that command, SET DATESTYLE TO 'European'; I tried to ...
1
vote
0answers
20 views

Query that fetchs average values over timestamp range of time, on a certain value of another pair of fields

This is the situation, over the time our table reached around 800 millions rows. And it is going to get bigger and bigger. It is a service that collects metrics of energy (voltage, power, current, etc)...
-1
votes
1answer
271 views

Select records based on earliest date in a linked record

I am a retired teacher working on my first MYSQL database as a personal project; please let me know if I've inadvertently violated conventions. And thank you! The database's domain is fiction stories,...
1
vote
1answer
45 views

What is the function that determines the maximum value in a series?

Say I have a table with columns C and D (among others but these are the relevant ones) with the date type. I am instructed to update the values in C to be 90 days before its corresponding value in D, ...
3
votes
1answer
2k views

Convert YYYY-MM-DD date (without time zone) to unix timestamp in Postgres

I've got a column of type date with the following value format: YYYY-MM-DD (eg. 2018-08-03). I want to get the unix timestamp equivalent of the dates, but without the timezone. F.e. the date 2018-08-...
1
vote
1answer
60 views

How to compute a membership expiration date on SQL

This is a follow up to this previously posted question. I'm using LibreOffice Base to work with some membership data. Further expansion might be possible in the future, but for now, the embedded ...
0
votes
1answer
1k views

Converting unix timestamp to an sql timestamp in postgres

I have passed Date.now() and other plain unix timestamps like 1534360109480 into sql and ran them through this. "@converted_date" = to_timestamp("@date") AT TIME ZONE 'UTC'; And it consistently ...
0
votes
1answer
193 views

How to create a trigger in oracle to prevent insert dates

I'm learning Oracle and I had a problem. Chat table: CREATE TABLE training ( id_training NUMBER, id_user NUMBER, start_training DATE, end_training DATE ); I want to ...
-2
votes
3answers
197 views

How to list the table based on week or month from range of dates?

I'm using MySQL. I have a table attendance which looks like Now i'm trying to display the leaves w.r.t. manager id using the query select att.mgr_assc_fk, att.id_emp_fk, sum(case when leave_typ = '...
1
vote
1answer
50 views

Finding which DATE columns have no time data using TRUNC

I'm in the process of converting an Oracle db to a PostgreSQL one. The script I used converted all Oracle's DATE columns into TIMESTAMPS, but I suspect that most of those do not hold any time data ...
1
vote
1answer
32 views

datetime row created

I have sqlserver DB , and there I have few tables. I have 1 table that not save each row what is the datetime the each row created. Is there any way to know when which row was created? Is there any ...
1
vote
2answers
5k views

What is the CURRENT_DATE or current date value function for SQL Server?

What is the SQL Server functionality that allows you to get the current date as a DATE type like the SQL Standard's CURRENT_DATE feature. What is the <current date value function> for SQL Server?...
0
votes
1answer
101 views

Count days between dates and find largest gap

I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like ...
5
votes
1answer
365 views

Getting year DATEPART from Unix date

In my database, the dates are retained as days elapsed since/before 01/01/1970, which I understand to be a Unix date format, eg. today is 23rd May 2018 and the value is 17674. I'm trying to run a ...