Questions tagged [datetime]

for questions specific to data types that contain both date and time parts.

Filter by
Sorted by
Tagged with
12
votes
1answer
26k views

How do I generate a date series in PostgreSQL?

If you're looking to generate a time series, see this question Let's say that I want to generate a series of dates between two dates. I see the function generate_series provides only Function ...
3
votes
1answer
2k views

Conversion of varchar data to datetime failing

We are in the process of moving data from a legacy table (all varchar fields) into a strongly typed counterpart cue cheering As part of this effort, we are taking data from the base Entity table and ...
4
votes
5answers
60k views

Convert varchar column to datetime

In SQL Server, I have a varchar column with value such as: 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 07/May/2012:00:06:22 How do I convert this column ...
25
votes
1answer
6k views

What date/time literal formats are LANGUAGE and DATEFORMAT safe?

It is easy to demonstrate that many date/time formats other than the following two are vulnerable to misinterpretation due to SET LANGUAGE, SET DATEFORMAT, or a login's default language: yyyyMMdd ...
5
votes
2answers
2k views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
5
votes
1answer
1k views

Leap second in Database system (Postgresql and SQL Server)

This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 23:...
4
votes
2answers
2k views

MySQL: "FROM_UNIXTIME(0)" gives "1970-01-01 01:00:00", which is 1 hour off

Context: MySQL version On MySQL 5.7.17 (as shown by SHOW VARIABLES LIKE "%version%";) Context: MySQL timezone settings and with SELECT @@global.time_zone, @@session.time_zone; yielding +-----------...
17
votes
1answer
125k views

MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

I'm using MySQL 5.6 and I have a program that runs the following SQL statement against my database: UPDATE `m_table` SET `s_time` = '2013-08-25T17:00:00+00:00' WHERE id = '123' Unforutnately, I get ...
14
votes
2answers
8k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
24
votes
6answers
34k views

Preferred way to store DateTime

We can store Date and Time information in a couple of ways. What is the best approach for storing DateTime information? Storing Date and Time in 2 separate columns or one column using DateTime? Can ...
6
votes
2answers
17k views

How to get minimum and maximum for grouped timestamps

I would love to know how to create a view that groups timestamps in 10 minute nearest 10 minute intervals and contains each minimum and maximum timestamp for each. So a table that looks like this: | ...
3
votes
2answers
34k views

Should I join datetime to a date using cast or range?

This question is a take-off from the excellent one posed here: Cast to date is sargable but is it a good idea? In my case, I am not concerned with the WHERE clause but in joining to an events table ...
21
votes
5answers
45k views

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My ...
9
votes
5answers
181k views

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I am trying to run a simple query to get all rows created in November: SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' AND '2014-11-30 23:59:59.997'; SMSS ...
8
votes
1answer
4k views

Why is GETUTCDATE earlier than SYSDATETIMEOFFSET?

Alternatively, how did Microsoft make time travel possible? Consider this code: DECLARE @Offset datetimeoffset = sysdatetimeoffset(); DECLARE @UTC datetime = getUTCdate(); DECLARE @UTCFromOffset ...
7
votes
4answers
74k views

get all dates in the current month

i have a query in MySQL which serves me very well by getting all the records within the current month; SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH); The ...
1
vote
2answers
3k views

Find the OverLapped (Common) Date Range from Group of Date Ranges

How can I find the Overlapped (common date range) from a group of Date Ranges given? The following table contains events and programmes (EID and PID respectively) CREATE TABLE #EventsTBL ( PID ...
6
votes
1answer
11k views

How to check the timezone for a given datetime?

A database / table I'm working it saves all its date values as datetime (without a timezone). How can I find out which timezone that date was in? For instance, the Central European Summer Time (+02) ...
5
votes
1answer
3k views

SQL Server 2005 - Best way to constraint a Datetime field as Date

Whereas: date field type starting with SQL Server 2008 Given a table: CREATE TABLE dbo.MyTable ( Code int NOT NULL, DateCode datetime NOT NULL ) CONSTRAINT [PK_MyTable] PRIMARY KEY ...
4
votes
1answer
983 views

What does 0 in DATEDIFF(MINUTE, 0, <Date>) actually mean?

So, our data team asked for some help in solving a problem they had. I eventually tracked it down to some really out of range data (1/1/0001) and a DATEDIFF function they were using. While I've ...
3
votes
3answers
2k views

Is timestamptz preferred when timezone logic is performed by the client?

I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with. The general advice seems to be always use timestamptz,...
3
votes
2answers
7k views

Converting string to date with timezone adjustment

I am attempting to convert stirngs to dates in mysql Thu Oct 23 16:46:47 2014 +02:00 Im unsure how to handle the +02:00, The rest is simple enough update logs set date = str_to_date(date_raw, '%a ...
3
votes
1answer
6k views

How to find XXth day of previous month in SQL server?

I have 2 parameters, an input date (DATETIME) input day of month (TINYINT) If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the output ...
2
votes
1answer
2k views

TSQL DateTime DataType - fractional seconds vs milliseconds

Wanting to get all records from a single day. So everything between 2013-03-05 00:00:00.000 and 2013-03-05 23:59:59.999. We're getting overlap at the backend with things that happened at 2013-03-06 00:...
1
vote
0answers
53 views

DateTime to Format: Implicit Conversion [duplicate]

I have a datetime I want to convert to a formatted date and time. The format is 109(9). I have always used: CONVERT(char(26), c1.DateTime, 109) Which works successfully, but at least on SQL Server ...
1
vote
1answer
271 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do -...
1
vote
2answers
232 views

PROCEDURE ANALYSE suggests to turn timestamp into CHAR(19)

Using Procedure Analyse() under MySQL 5.6.30 on a table with about 4 million rows makes the following recommendations: change timestamp to char(19) tinyint to ENUM (I noticed it's overzealous with ...
1
vote
1answer
53 views

Office Hours in PostgreSQL

How to store office hours in PostgreSQL rows, so that I can calculate the office hours. Example: We have open from 9:00 to 18:00 from Monday till Friday. Saturday we have open from 10:00 to 15:00 ...
1
vote
1answer
363 views

Why do those Update statements fail using a datetime format?

Given: Query #1: begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-12-25 07:00:00.000'; rollback Query #2: (changing month/day) begin tran UPDATE [dbo].[t] SET [mydatetime]='2011-25-12 07:00:00....
1
vote
3answers
3k views

What happens when we take a timediff between timestamp and datetime

Say I have following table in mysql (5.5+): CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL, `x_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `y_time`...
0
votes
2answers
528 views

Converting from char(26) to datetime2

In course of a change I migrated a database from Oracle 12.1 to MSSQL 2016. Since the two databases handle date differently, I now need to move the fields from char(26) to datetime2. Output format is ...
0
votes
1answer
64 views

What exactly is the datatype input into a DATETIME value in MySQL

I am trying to get data from a PLC directly into a MySQL 8.0 database (using MySQL Workbench). I need to know what data type the input value is for a column set to DATETIME so I can send in the right ...